[转]T-SQL DateTime相关的函数使用示例
在T-SQL中经常会用到与DateTime相关的函数，现将常用函数做一下汇总，以备忘。
SQL DAY() – MONTH( ) – YEAR()
DAY('2008-09-30') = 30
MONTH('2008-09-30') = 9
YEAR('2008-09-30') = 2008
SQL DATEPART()
DATEPART(day, '2008-09-30 11:35:00.1234567') = 30
DATEPART(month, '2008-09-30 11:35:00.1234567') =9
DATEPART(year, '2008-09-30 11:35:00.1234567') = 2008
DATEPART(hour, '2008-09-30 11:35:00.1234567') = 11
DATEPART(minute, '2008-09-30 11:35:00.1234567') = 35
DATEPART(second, '2008-09-30 11:35:00.1234567') = 0
DATEPART(quarter, '2008-09-30 11:35:00.1234567') = 3
DATEPART(dayofyear, '2008-09-30 11:35:00.1234567') =273
DATEPART(week, '2008-09-30 11:35:00.1234567') = 40
DATEPART(weekday, '2008-09-30 11:35:00.1234567') =7
DATEPART(millisecond, '2008-09-30 11:35:00.1234567') =123
DATEPART(microsecond, '2008-09-30 11:35:00.1234567') = 123456
DATEPART(nanosecond, '2008-09-30 11:35:00.1234567') = 123456700
DATEPART(tzoffset, '2008-09-30 11:35:00.1234567 -07:00') = -420
SQL DATEADD()
DATEADD(day, 1, '2008-09-30 11:35:00') = 2008-10-30 01:35:00.000
DATEADD(month, 1, '2008-09-30 11:35:00') = 2008-10-30 11:35:00.000
DATEADD(year, 1, '2008-09-30 11:35:00') = 2009-09-30 11:35:00.000
DATEADD(hour, 1, '2008-09-30 11:35:00') = 2008-09-30 12:35:00.000
DATEADD(minute, 1, '2008-09-30 11:35:00') = 2008-09-30 11:36:00.000
DATEADD(second, 1, '2008-09-30 11:35:00') = 2008-09-30 11:35:01.000
DATEADD(quarter, 1, '2008-09-30 11:35:00') =2008-12-30 11:35:00.000
DATEADD(week, 1, '2008-09-30 11:35:00') = 2008-10-07 11:35:00.000
DATEADD(month, -1, '2008-09-30 11:35:00') = 2008-08-30 11:35:00.000
DATEADD(year, 1.5 , '2008-09-30 11:35:00') = 2009-09-30 11:35:00.000
SQL DATENAME()
DATENAME(day, '2008-09-30 11:35:00.1234567') = 30
DATENAME(month, '2008-09-30 11:35:00.1234567') =September
DATENAME(year, '2008-09-30 11:35:00.1234567') = 2008
DATENAME(hour, '2008-09-30 11:35:00.1234567') = 11
DATENAME(minute, '2008-09-30 11:35:00.1234567') = 35
DATENAME(second, '2008-09-30 11:35:00.1234567') = 0
DATENAME(quarter, '2008-09-30 11:35:00.1234567') = 3
DATENAME(dayofyear, '2008-09-30 11:35:00.1234567') =273
DATENAME(week, '2008-09-30 11:35:00.1234567') = 40
DATENAME(weekday, '2008-09-30 11:35:00.1234567') =Saturday
DATENAME(millisecond, '2008-09-30 11:35:00.1234567') =123
DATENAME(microsecond, '2008-09-30 11:35:00.1234567') = 123456
DATENAME(nanosecond, '2008-09-30 11:35:00.1234567') = 123456700
DATENAME(tzoffset, '2008-09-30 11:35:00.1234567 -07:00') = -07:00
SQL DATEDIFF()
DATEDIFF(day, '2007-12-01' , '2008-09-30') = 303
DATEDIFF(month, '2007-12-01' , '2008-09-30') = 9
DATEDIFF(year, '2007-12-01' , '2008-09-30') = 1
DATEDIFF(hour, '06:46:45' , '11:35:00') = 5
DATEDIFF(minute, '06:46:45' , '11:35:00') = 289
DATEDIFF(second, '06:46:45' , '11:35:00') = 17295
DATEDIFF(quarter, '2007-12-01' , '2008-09-30') = 3
DATEDIFF(week, '2007-12-01' , '2008-09-30') = 44
DATEDIFF(hour, '2008-09-30' , '2007-12-01') = -303
SOME OTHER SQL DATE/TIME RELATED FUNCTIONS
GETDATE()
GETUTCDATE()
SYSDATETIME()
SYSUTCDATETIME()
SYSUTCDATETIMEOFFSET()
DATEADD(datepart,NUMBER,date)
DATEADIFF(datepart, startdate,enddate)
TODATETIMEOFFSET(datetime2,tzoffset)
SWITCHOFFSET(datetimeoffset,tzoffest)
ISDATE(expression)
Adding to this - Calculate no of Days between two dates excluding Weekends.
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2010/05/01'
SET @EndDate = '2010/05/11'
SELECT (DATEDIFF(dd, @StartDate, @EndDate) + 1)-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) -- 7
