locked
T-SQL datetime related functions examples RRS feed

  • Question

  • User-589637085 posted

    Just posting it for the reference, found these functions online but here and there. So compiling it in one place (got from one of the books)


    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)

    Tuesday, May 25, 2010 8:09 PM

All replies

  • User406085191 posted

    Thank u Helpjet for providing nice info

     

    Wednesday, May 26, 2010 1:58 AM
  • User-457353196 posted

    Very Useful.

    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

     

    Shiva Mamidi

     

     

    Wednesday, May 26, 2010 3:09 PM