資料庫中substring的使用方法 CONVERT(varchar(12) , getdate(), 112 )

Sqlserver中經常要操作一些時間類型的字段轉換，我又不太記得住，是以搜集了以下的一些SqlserverConvertDateTime相關的資料發表在自己的小站裡，友善自己以後要用的時候尋找，望對大家也有幫助．

将sqlserver中table表的［datetime］字段值‘2007-11-0716:41:35.033’ 改為‘2007-11-0700：00：00‘去除了時分秒．［datetime］字段要為datetime類型的哦． UPDATE table SET[datetime]= Convert(char(11),[datetime],120)

1、擷取目前日期利用　convert　來轉換成我們需要的datetime格式． 貌似 oracle的 PLSQl中不能直接用呀。。。隻适應與 sql server 之類的資料庫？？？

select CONVERT(varchar(12) , getdate(), 112 ) 類似oracle 中的 to_char(xsdate,'yyyymm')

20040912

------------------------------------------------------------

select CONVERT(varchar(12) , getdate(), 102 )

2004.09.12

select CONVERT(varchar(12) , getdate(), 101 )

09/12/2004

select CONVERT(varchar(12) , getdate(), 103 )

12/09/2004

select CONVERT(varchar(12) , getdate(), 104 )

12.09.2004

select CONVERT(varchar(12) , getdate(), 105 )

12-09-2004

select CONVERT(varchar(12) , getdate(), 106 )

12 09 2004

select CONVERT(varchar(12) , getdate(), 107 )

09 12, 2004

select CONVERT(varchar(12) , getdate(), 108 )

11:06:08

select CONVERT(varchar(12) , getdate(), 109 )

09 12 2004 1

select CONVERT(varchar(12) , getdate(), 110 )

09-12-2004

select CONVERT(varchar(12) , getdate(), 113 )

12 09 2004 1

select CONVERT(varchar(12) , getdate(), 114 )

11:06:08.177

declare @dateTime DateTime--定義一個datetime的變量

set @dateTime=getdate();　--擷取系統目前時間，并指派給@dateTime字段

--短日期格式：yyyy-m-d

SELECTREPLACE(CONVERT(varchar(10),@dateTime,120),N'-0','-')

--長日期格式：yyyy年mm月dd日

SELECTSTUFF(STUFF(CONVERT(char(8),@dateTime,112),5,0,N'年'),8,0,N'月')+N'日'

--長日期格式：yyyy年m月d日

SELECT DATENAME(Year,@dateTime)+N'年'+CAST(DATEPART(Month,@dateTime)AS varchar)+N'月'+DATENAME(Day,@dateTime)+N'日'

--完整日期+時間格式：yyyy-mm-dd hh:mi:ss:mmm

SELECTCONVERT(char(11),@dateTime,120)+CONVERT(char(12),@dateTime,114)

------------------------------------------------------------------------------------------------

2、substring ，該函數用來求一個字元串的字串,該函數的使用頻率很高。也不是oracle中的用法

示例字元串：”2011-11-17”

在Oracle中求字元串的函數為：substr

The syntax for the substr function is:

substr( string, start_position, [ length ] )

string：源字元串

start_position：子串第一個字元在源字元串中的起始位置

length：子串長度

測試結果：

1.

substr('2011-11-17',0,7)

2011-11

2.

substr('2011-11-17',1,7)

3.

substr('2011-11-17',1,10)

2011-11-17

4.

substr('2011-11-17',1,11)

5.

substr('2011-11-17',-1,7)

7

6.

substr('2011-11-17',-8,8)

11-11-17

substr('2011-11-17',-10,7)

8.

substr('2011-11-17',-11,7)

null

9.

substr('2011-11-17',-11)

10.

substr('2011-11-17',-1)

11.

substr('2011-11-17',6)

11-17

12.

substr('2011-11-17',11)

13.

substr (‘2011-11-17’,1,null)

14.

substr (‘2011-11-17’,null,1)

15.

substr (‘2011-11-17’,null,null)

16.

substr ('2011-11-17',1,0)

17.

substr ('2011-11-17',1,-1)

18.

substr ('2011-11-17',11,11)

Oracle中規定：

1） start_position==0時，子串的起始位置為1，即從第一個字元開始；

2） start_position<0時，字串的起始位置從字元串尾部開始後推。

3） length參數可以預設

3、日期推算處理

oracle 時間函數(sysdate) to_char才是王道：oracle

1）:取得目前日期是本月的第幾周

SQL> select to_char(sysdate,'YYYYMMDD W HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'YY

-------------------

20030327 4 18:16:09

SQL> select to_char(sysdate,'W') from dual;

T

-

2:取得目前日期是一個星期中的第幾天,注意星期日是第一天

SQL> select sysdate,to_char(sysdate,'D') from dual;

SYSDATE T

--------- -

27-MAR-03 5

　　類似:

select to_char(sysdate,'yyyy') from dual; --年

select to_char(sysdate,'Q' from dual; --季

select to_char(sysdate,'mm') from dual; --月

select to_char(sysdate,'dd') from dual; --日

ddd 年中的第幾天

WW 年中的第幾個星期

W 該月中第幾個星期

D 周中的星期幾

hh 小時(12)

hh24 小時(24)

Mi 分

ss 秒

3:取目前日期是星期幾中文顯示:

SQL> select to_char(sysdate,'day') from dual;

TO_CHAR(SYSDATE,'DAY')

----------------------

星期四

4:如果一個表在一個date類型的字段上面建立了索引，如何使用

alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

5: 得到目前的日期

select sysdate from dual;

6: 得到當天淩晨0點0分0秒的日期

select trunc(sysdate) from dual;

-- 得到這天的最後一秒

select trunc(sysdate) + 0.99999 from dual;

-- 得到小時的具體數值

select trunc(sysdate) + 1/24 from dual;

select trunc(sysdate) + 7/24 from dual;

7.得到明天淩晨0點0分0秒的日期

select trunc(sysdate+1) from dual;

select trunc(sysdate)+1 from dual;

8: 本月一日的日期

select trunc(sysdate,'mm') from dual;

9:得到下月一日的日期

select trunc(add_months(sysdate,1),'mm') from dual;

10:傳回目前月的最後一天?

select last_day(sysdate) from dual;

select last_day(trunc(sysdate)) from dual;

select trunc(last_day(sysdate)) from dual;

select trunc(add_months(sysdate,1),'mm') - 1 from dual;

11: 得到一年的每一天

select trunc(sysdate,'yyyy')+ rn -1 date0

from

(select rownum rn from all_objects

where rownum<366);

12:今天是今年的第N天

SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL;

13:如何在給現有的日期加上2年

select add_months(sysdate,24) from dual;

14:判斷某一日子所在年分是否為潤年

select decode(to_char(last_day(trunc(sysdate,'y')+31),'dd'),'29','閏年','平年') from dual;

15:判斷兩年後是否為潤年

select decode(to_char(last_day(trunc(add_months(sysdate,24),'y')+31),'dd'),'29','閏年','平年') from dual;

16）:得到日期的季度

select ceil(to_number(to_char(sysdate,'mm'))/3) from dual;

select to_char(sysdate, 'Q') from dual;

四）其餘時間處理方法：

DECLARE @dt datetime

SET @dt=GETDATE()

DECLARE @number int

SET @number=3

--1．指定日期該年的第一天或最後一天

--A. 年的第一天

SELECT CONVERT(char(5),@dt,120)+'1-1'

--B. 年的最後一天

SELECT CONVERT(char(5),@dt,120)+'12-31'

--2．指定日期所在季度的第一天或最後一天

--A. 季度的第一天

SELECT CONVERT(datetime,

CONVERT(char(8),

DATEADD(Month,

DATEPART(Quarter,@dt)*3-Month(@dt)-2,

@dt),

120)+'1')

--B. 季度的最後一天（CASE判斷法）

DATEPART(Quarter,@dt)*3-Month(@dt),

120)

+CASE WHEN DATEPART(Quarter,@dt) in(1,4)

THEN '31'ELSE '30' END)

--C. 季度的最後一天（直接推算法）

SELECT DATEADD(Day,-1,

1+DATEPART(Quarter,@dt)*3-Month(@dt),

--3．指定日期所在月份的第一天或最後一天

--A. 月的第一天

SELECTCONVERT(datetime,CONVERT(char(8),@dt,120)+'1')

--B. 月的最後一天

SELECTDATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')

--C. 月的最後一天（容易使用的錯誤方法）

SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))

--4．指定日期所在周的任意一天

SELECTDATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)

--5．指定日期所在周的任意星期幾

--A. 星期天做為一周的第1天

SELECTDATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)

--B. 星期一做為一周的第1天

SELECTDATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)