(MSSQL)datetime處理

 

現在時間

SELECT GETDATE()

取得現在年、月、日

SELECT YEAR(GETDATE())
SELECT MONTH(GETDATE())
SELECT DAY(GETDATE())

DATEPART

Datepart	Description
year, yyyy, yy	年
month, mm, m	月
day, dd, d	日
hour, hh	時
minute, n	分
second, ss, s	秒
millisecond, ms	毫秒
microsecond, mcs	微秒
nanosecond, ns	毫微秒
quarter, qq, q	季
dayofyear, dy, y	一年中的第幾天
week, wk, ww	一年中的第幾週
weekday, dw	星期幾
TZoffset, tz	時區 time zone offset，單位是分鐘

SELECT DATEPART(yy,'2010-10-30 12:20:30')
-- 2010
SELECT DATEPART(m,'2010-10-30 12:20:30')
-- 10
SELECT DATEPART(d,'2010-10-30 12:20:30')
-- 30
SELECT DATEPART(hh,'2010-10-30 12:20:30')
-- 12
SELECT DATEPART(mi,'2010-10-30 12:20:30')
-- 20
SELECT DATEPART(ww,'2010-10-30 12:20:30')
-- 44
SELECT DATEPART(q,'2010-10-30 12:20:30')
-- 4

-- 注意，y不是年，是一年中的第幾天
SELECT DATEPART(y,'2010-10-30 12:20:30')
-- 303

SELECT convert(varchar, getdate(), 100) - mon dd yyyy hh:mmAM (or PM)
SELECT convert(varchar, getdate(), 101) - mm/dd/yyyy
SELECT convert(varchar, getdate(), 102) - yyyy.mm.dd
SELECT convert(varchar, getdate(), 103) - dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) - dd.mm.yyyy
SELECT convert(varchar, getdate(), 105) - dd-mm-yyyy
SELECT convert(varchar, getdate(), 106) - dd mon yyyy
SELECT convert(varchar, getdate(), 107) - mon dd, yyyy
SELECT convert(varchar, getdate(), 108) - hh:mm:ss
SELECT convert(varchar, getdate(), 109) - mon dd yyyy hh:mm:ss:mmmAM (or PM) 
SELECT convert(varchar, getdate(), 110) - mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) - yyyy/mm/dd
SELECT convert(varchar, getdate(), 112) - yyyymmdd
SELECT convert(varchar, getdate(), 113) - dd mon yyyy hh:mm:ss:mmm
SELECT convert(varchar, getdate(), 114) - hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 120) - yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) - yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) - yyyy-mm-ddThh:mm:ss.mmm
SELECT replace(convert(varchar, getdate(), 111), -/-, - -) - yyyy mm dd
SELECT convert(varchar(7), getdate(), 126)                 - yyyy-mm
SELECT right(convert(varchar, getdate(), 106), 8)          - mon yyyy
SELECT format(getdate(),'yyyyMMddHHmmssffff') -- 2014-04-17 13:55:12 => 20140417135512

DATEDIFF

DATEDIFF Syntax

DATEDIFF是計算兩個日期間的間隔，傳回帶正負號的整數.

DATEDIFF(datepart, startdate, enddate)

datepart：startdate 和 enddate 之間差異的單位。datepart值不能在變數中指定，也不能是以引號括住的字串 (例如 'month')。
startdate：開始日期
enddate：結束日期

datepart Arguments

datepart name	datepart 縮寫
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy
day	dd, d
week	wk, ww
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

startdate/enddate 可解析格式

• date
• datetime
• datetimeoffset
• datetime2
• smalldatetime
• time

DATEADD

DATEADD會將 數值 (帶正負號的整數) 加入輸入日期的datepart，並傳回修改的日期/時間值。

DATEADD Syntax

DATEADD (datepart , number , date )

datepart：startdate 和 enddate 之間差異的單位
number：指定的數值(帶正負號的整數)
date：要被加上的原始日期

datepart arguments

同 DATEDIFF arguments

date 可解析格式

同 DATEDIFF startdate/enddate 可解析格式

DATEADD/DATEDIFF綜合應用

Last Day of Previous Month

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AS LastDay_PreviousMonth

Last Day of Current Month

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) AS LastDay_CurrentMonth

Last Day of Next Month

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)) AS LastDay_NextMonth