TSQL 日期轉換, 格式, 計算

年紀大了，稍微整理一下TSQL 日期轉換, 格式, 計算

日期轉換
我們如果要把字串轉為日期可利用Convert或Cast，差異在語法不同，如果是日期轉字串，要指定格式的話，就只能用Convert，以下是字串轉日期的範例，第一個參數是是資料型態，自然是 Datetime，第二個參數則是日期的字串：
select convert(datetime,'2013/05/01 14:00:00.123')
結果為:
2013-05-01 14:00:00.123
日期格式
這裡只能用 Convert，第一個參數為varchar，第二個參數是日期，第三個參數為style參數，我們的Style從100~121

declare @dt datetime
set @dt = '2013/05/01 14:00:00.123'

Print '100 : ' + convert(varchar,@dt,100)
Print '101 : ' + convert(varchar,@dt,101)
Print '102 : ' + convert(varchar,@dt,102)
Print '103 : ' + convert(varchar,@dt,103)
Print '104 : ' + convert(varchar,@dt,104)
Print '105 : ' + convert(varchar,@dt,105)
Print '106 : ' + convert(varchar,@dt,106)
Print '107 : ' + convert(varchar,@dt,107)
Print '108 : ' + convert(varchar,@dt,108)
Print '109 : ' + convert(varchar,@dt,109)
Print '110 : ' + convert(varchar,@dt,110)
Print '111 : ' + convert(varchar,@dt,111)
Print '112 : ' + convert(varchar,@dt,112)
Print '113 : ' + convert(varchar,@dt,113)
Print '114 : ' + convert(varchar,@dt,114)
Print '120 : ' + convert(varchar,@dt,120)
Print '121 : ' + convert(varchar,@dt,121)
Print '126 : ' + convert(varchar,@dt,126)

結果:


100 : 05  1 2013  2:00PM

101 : 05/01/2013


102 : 2013.05.01


103 : 01/05/2013


104 : 01.05.2013


105 : 01-05-2013


106 : 01 05 2013


107 : 05 01, 2013


108 : 14:00:00


109 : 05  1 2013  2:00:00:123PM


110 : 05-01-2013


111 : 2013/05/01


112 : 20130501


113 : 01 05 2013 14:00:00:123


114 : 14:00:00:123


120 : 2013-05-01 14:00:00


121 : 2013-05-01 14:00:00.123


126 : 2013-05-01T14:00:00.123


日期推算


最後談一下計算部分，如果我們要加1.5 小時，因為有小數點，所以要把小時轉成分鐘，再做運算，使用到的函式為Dateadd，這計算的函式，第一個參數為Datepart，第二個參數是要加減的數值，這裡數值會依照給的單位進行日期運算，最後一個參數就是原始日期，SQL如下：


declare @dt datetime

set @dt = '2013/05/01 14:00:00'


select dateadd(minute,-2.5 * 60, @dt)




Datepart有以下幾種





底下是常用的一些的函式

--今年的的第一天和最後一天   
SELECT CONVERT(char(5),GETDATE(),120)+'1-1' AS FDATE,CONVERT(char(5),GETDATE(),120)+'12-31' LDTAE  
 
--本季度的第一天和最後一天
--  方法1
SELECT CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART
(Quarter,GETDATE())*3-Month(GETDATE())-2,GETDATE()),120)+'1') AS FDATE 
 ,CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART
(Quarter,GETDATE())*3-Month(GETDATE()),GETDATE()),120) 
 +CASE WHEN DATEPART(Quarter,GETDATE()) in(1,4)  THEN '31'ELSE '30' END) 
AS LDATE 

 --  方法2  
SELECT CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART
(Quarter,GETDATE())*3-Month(GETDATE())-2,GETDATE()),120)+'1') AS FDATE
 ,DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1+DATEPART
(Quarter,GETDATE())*3-Month(GETDATE()),GETDATE()),120)+'1') AS LDATE

--本月份的第一天和最後一天  
SELECT CONVERT(datetime,CONVERT(char(8),GETDATE(),120)+'1') AS 
FDATE,DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,GETDATE()),120)+'1') 
AS LDATE  

--本週的星期日~五，對應的日期+現在時間 
SELECT DATEADD(Day,0-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
SELECT DATEADD(Day,1-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())  
SELECT DATEADD(Day,2-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())
SELECT DATEADD(Day,3-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE()) 
SELECT DATEADD(Day,4-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE()) 
SELECT DATEADD(Day,5-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE()) 
SELECT DATEADD(Day,6-(DATEPART(Weekday,GETDATE())+@@DATEFIRST-1)%7,GETDATE())

--昨天  
SELECT DATEADD(Day,-1,GETDATE()) 

--前天  
SELECT DATEADD(Day,-2,GETDATE())

--明天 
SELECT DATEADD(Day,1,GETDATE())  

 

日期計算


這裡會使用DateDiff，語法如下


DATEDIFF ( datepart , startdate , enddate )


例:


declare @dt1 datetime

declare @dt2 datetime


set @dt1 = '2005-11-30 23:59:59.999'


set @dt2 = '2006-01-01 00:00:00.000'


PRINT DATEDIFF(year,@dt1,@dt2);

PRINT DATEDIFF(month,@dt1,@dt2);


PRINT DATEDIFF(dayofyear,@dt1,@dt2);


PRINT DATEDIFF(day,@dt1,@dt2);


PRINT DATEDIFF(minute,@dt1,@dt2);


結果


1

1


31


31


44640

 

參考

CAST and CONVERT (Transact-SQL)


DATEADD (Transact-SQL)


DATEDIFF (Transact-SQL)