T-SQL DateTime相关的函数使用示例

来源：互联网发布：cos定制知乎编辑：程序博客网时间：2024/05/17 08:17

src: http://forums.asp.net/t/1561997.aspx

SQL DAY() -- MONTH( ) – YEAR()

DAY(‘2008-09-30’) = 30MONTH(‘2008-09-30’) = 9YEAR(‘2008-09-30’) = 2008

SQL DATEPART()

DATEPART(day, ‘2008-09-30 11:35:00.1234567’) = 30DATEPART(month, ‘2008-09-30 11:35:00.1234567’) =9DATEPART(year, ‘2008-09-30 11:35:00.1234567’) = 2008DATEPART(hour, ‘2008-09-30 11:35:00.1234567’) = 11DATEPART(minute, ‘2008-09-30 11:35:00.1234567’) = 35DATEPART(second, ‘2008-09-30 11:35:00.1234567’) = 0DATEPART(quarter, ‘2008-09-30 11:35:00.1234567’) = 3DATEPART(dayofyear, ‘2008-09-30 11:35:00.1234567’) =273DATEPART(week, ‘2008-09-30 11:35:00.1234567’) = 40DATEPART(weekday, ‘2008-09-30 11:35:00.1234567’) =7DATEPART(millisecond, ‘2008-09-30 11:35:00.1234567’) =123DATEPART(microsecond, ‘2008-09-30 11:35:00.1234567’) = 123456DATEPART(nanosecond, ‘2008-09-30 11:35:00.1234567’) = 123456700DATEPART(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.000DATEADD(month, 1, ‘2008-09-30 11:35:00’) = 2008-10-30  11:35:00.000DATEADD(year, 1, ‘2008-09-30 11:35:00’) = 2009-09-30  11:35:00.000DATEADD(hour, 1, ‘2008-09-30 11:35:00’) = 2008-09-30  12:35:00.000DATEADD(minute, 1, ‘2008-09-30 11:35:00’) = 2008-09-30  11:36:00.000DATEADD(second, 1, ‘2008-09-30 11:35:00’) = 2008-09-30  11:35:01.000DATEADD(quarter, 1, ‘2008-09-30 11:35:00’) =2008-12-30  11:35:00.000DATEADD(week, 1, ‘2008-09-30 11:35:00’) = 2008-10-07  11:35:00.000DATEADD(month, -1, ‘2008-09-30 11:35:00’) = 2008-08-30  11:35:00.000DATEADD(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’) = 30DATENAME(month, ‘2008-09-30 11:35:00.1234567’) =SeptemberDATENAME(year, ‘2008-09-30 11:35:00.1234567’) = 2008DATENAME(hour, ‘2008-09-30 11:35:00.1234567’) = 11DATENAME(minute, ‘2008-09-30 11:35:00.1234567’) = 35DATENAME(second, ‘2008-09-30 11:35:00.1234567’) = 0DATENAME(quarter, ‘2008-09-30 11:35:00.1234567’) = 3DATENAME(dayofyear, ‘2008-09-30 11:35:00.1234567’) =273DATENAME(week, ‘2008-09-30 11:35:00.1234567’) = 40DATENAME(weekday, ‘2008-09-30 11:35:00.1234567’) =SaturdayDATENAME(millisecond, ‘2008-09-30 11:35:00.1234567’) =123DATENAME(microsecond, ‘2008-09-30 11:35:00.1234567’) = 123456DATENAME(nanosecond, ‘2008-09-30 11:35:00.1234567’) = 123456700DATENAME(tzoffset, ‘2008-09-30 11:35:00.1234567 -07:00’) = -07:00

SQL DATEDIFF()

DATEDIFF(day, ‘2007-12-01’ , ’2008-09-30’) = 303DATEDIFF(month, ‘2007-12-01’ , ’2008-09-30’) = 9DATEDIFF(year, ‘2007-12-01’ , ’2008-09-30’) = 1DATEDIFF(hour, ’06:46:45’ , ’11:35:00’) =  5DATEDIFF(minute, ’06:46:45’ , ’11:35:00’) =  289DATEDIFF(second, ’06:46:45’ , ’11:35:00’) =  17295DATEDIFF(quarter, ‘2007-12-01’ , ’2008-09-30’) =  3DATEDIFF(week, ‘2007-12-01’ , ’2008-09-30’) =  44DATEDIFF(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)

Calculate no of Days between two dates excluding Weekends.

DECLARE @StartDate DATETIMEDECLARE @EndDate DATETIMESET @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

0 0