在使用SQL組資料時，有時候會針對資料的時間去找出對應的月初、月末等時間點的值
以下記錄一下幾個常用的SQL 時間函數
- DATEADD ( datepart , number , date )
對指定日期date添加number個數量的datepart時間單位。
EX: SELECT DATEADD( year , 1 , '2014-06-03' ),DATEADD( month, 1 , '2014-06-03' ),DATEADD( day, 1 , '2014-06-03' )
=>2015-06-03 00:00:00.000 2014-07-03 00:00:00.000 2014-06-04 00:00:00.000
即可找出去年、上月、昨天的時間點值
- DATEDIFF ( datepart , startdate , enddate )
計算出起始時間(startdate)與結束時間(enddate)差距多少時間單位(datepart)的數量
EX: SELECT DATEDIFF( year , '1987-01-27' , '2014-06-03' ),DATEDIFF( month , '1987-01-27' , '2014-06-03' ),DATEDIFF( day , '1987-01-27' , '2014-06-03' )
=>相差 27 329 9989
即可找出差距多少個時間單位
- DATENAME ( datepart , date )
顯示指定時間(date)所指定時間單位(datepart)的字串
EX: SELECT DATENAME( YEAR , '2014-06-03' ),DATENAME( QUARTER , '2014-06-03' ),DATENAME( MONTH , '2014-06-03' ),DATENAME( WEEKDAY , '2014-06-03' )
=>2014 2 六月 星期二
雖然與YEAR()、MONTH()等函數相似，但出來的值是字串而非數字，並且對於中文的星期、月份換算較為方便使用
- DATEPART ( datepart , date )
顯示指定時間(date)所指定時間單位(datepart)的整數
EX: SELECT DATEPART( YEAR , '2014-06-03' ),DATEPART( QUARTER , '2014-06-03' ),DATEPART( MONTH , '2014-06-03' ),DATEPART( WEEKDAY , '2014-06-03' )
=>2014 2 6 3
與DATENAME功能相似，但出來的是整數值，而星期部分一星期是由周日開始，所以星期二是一星期的第三天
根據上方幾個常用的日期函數(支援SQL2005以後版本)，可做下列的日期計算
DECLARE @Date DATETIME = GETDATE();
SELECT @Date AS '日前時間'
,DATEADD(DD,-1,@Date) AS '昨天'
,DATEADD(DD,1,@Date) AS '明天'
/*月相關*/
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0) AS '月初'
,DATEADD(DD,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精確到天)'
,DATEADD(SS,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精確到datetime的小數位)'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)-1,0) AS '上月第一天'
,DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) AS '上月最後一天'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+1,0) AS '下月第一天'
,DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))) AS '下月最后一天'
/*周相關*/
,DATEADD(WEEKDAY,1-DATEPART(WEEKDAY,@Date),@Date) AS '本周第一天(星期日)'
,DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),-1) AS '所在星期的星期日'
,DATEADD(DAY,2-DATEPART(WEEKDAY,@Date),@Date) AS '所在星期的第二天'
,DATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '上個星期第一天(星期日)'
,DATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '下個星期第一天(星期日)'
,DATENAME(WEEKDAY,@Date) AS '本日是星期幾'
,DATEPART(WEEKDAY,@Date) AS '本日是星期幾'
/*年相關*/
,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) AS '年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-1,@Date),-1) AS '年末'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date)-1,0) AS '去年年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date),-1) AS '去年年末'
,DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0) AS '明年年初'
,DATEADD(YEAR,1+DATEDIFF(YEAR,-1,@Date),-1) AS '明年年末'
/*季相關*/
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0) AS '本季季初'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),-1) AS '本季季末'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date)-1,0) AS '上季季初'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),-1) AS '上季季末'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0) AS '下季季初'
,DATEADD(QUARTER,2+DATEDIFF(QUARTER,0,@Date),-1) AS '下季季末'
參考出處：http://bbs.csdn.net/topics/390304212?page=1
留言列表
