DATE Samples

-- Selecting with CONVERT into different styles

-- Note: Only Japan & ISO styles can be used in ORDER BY for sorting

SELECT  DISTINCT TOP(2)

     Italy  = CONVERT(char(10), OrderDate, 105)

   , USA    = CONVERT(char(10), OrderDate, 110)

   , Japan  = CONVERT(char(10), OrderDate, 111)

   , ISO    = CONVERT(char(8),  OrderDate, 112)

FROM AdventureWorks2008.Purchasing.PurchaseOrderHeader

/* Results

Italy       USA         Japan       ISO

17-05-2011  05-17-2011  2011/05/17  20110517

31-05-2011  05-31-2011  2011/05/31  20110531

*/

/***** SELECTED SQL DATETIME FORMATS WITH NAMES *****/ 

-- US-Style format: 10/23/2011

select [US-Style]=CONVERT(varchar,GETDATE(),101)  

-- UK-Style (British/French) format: 23/10/2011

select [UK-Style]=CONVERT(varchar,GETDATE(),103) 

-- German format: 23.10.2011

select [German]=CONVERT(varchar,GETDATE(),104)

-- ISO format: 20111023

select ISO=convert(varchar,GETDATE(),112) 

-- ISO8601 format: 2011-10-23T19:20:16.003

select [ISO8601]=convert(varchar,GETDATE(),126)

-- SQL Date and Time Functions

-- SQL CURRENT_TIMESTAMP function

-- local NYC - EST - Eastern Standard Time zone

SELECT CURRENT_TIMESTAMP                        -- 2014-01-05 07:02:10.577

-- SQL DATEADD Date and Time Function

SELECT DATEADD(month,2,'2014-12-09')            -- 2015-02-09 00:00:00.000

-- SQL DATEDIFF Date and Time Function

SELECT DATEDIFF(day,'2014-12-09','2015-02-09')  -- 62

-- SQL DATENAME Date and Time Function

SELECT DATENAME(month, '2014-12-09')            -- December

SELECT DATENAME(weekday, '2014-12-09')          -- Sunday

-- SQL DATEPART Date and Time Function

SELECT DATEPART(month, '2014-12-09')            -- 12

-- SQL DAY Date and Time Function

SELECT DAY('2014-12-09')                        -- 9

-- SQL GETDATE Date and Time Function

-- local NYC - EST - Eastern Standard Time zone

SELECT GETDATE()                                -- 2014-01-05 07:02:10.577

-- SQL GETUTCDATE Date and Time Function

-- London - Greenwich Mean Time

SELECT GETUTCDATE()                             -- 2014-01-05 12:02:10.577

-- SQL ISDATE Date and Time Function

SELECT ISDATE('2014-12-09')                     -- 1

SELECT ISDATE('2014-18-09')                     -- 0

-- SQL MONTH Date and Time Function

SELECT MONTH('2014-12-09')                      -- 12

-- SQL SYSDATETIME() Date and Time Function

SELECT SYSDATETIME()                          -- 2014-01-05 07:02:10.5777500

-- SQL SYSUTCDATETIME() Date and Time Function

SELECT SYSUTCDATETIME()                       -- 2014-01-05 12:02:10.5777500

-- SQL YEAR Date and Time Function

SELECT YEAR('2014-12-09')                       -- 2014

-- Converting seconds to HH:MM:SS format

DECLARE @Seconds int = 20000

SELECT TimeSpan=right('0' +rtrim(@Seconds / 3600),2) + ':' +

right('0' + rtrim((@Seconds % 3600) / 60),2) + ':' +

right('0' + rtrim(@Seconds % 60),2)

-- Result: 05:33:20 

-- Test result

SELECT 5*3600 + 33*60 + 20  -- Result: 20000

-- SQL DATEPART

----------

SELECT [Date Part]=DATEPART(Year, getdate())

-- 2012

SELECT [Date Part]=DATEPART(yy, getdate())

SELECT [Date Part]=DATEPART(yyyy, getdate())

SELECT [Date Part]=DATEPART(quarter, getdate())

-- 1

SELECT [Date Part]=DATEPART(qq, getdate())

SELECT [Date Part]=DATEPART(q, getdate())

SELECT [Date Part]=DATEPART(month, getdate())

-- 12

SELECT [Date Part]=DATEPART(mm, getdate())

SELECT [Date Part]=DATEPART(m, getdate())

-- SQL Julian date

SELECT [Date Part]=DATEPART(dayofyear, getdate())

-- 335

SELECT [Date Part]=DATEPART(dy, getdate())

SELECT [Date Part]=DATEPART(y, getdate())

-- SQL day of month

SELECT [Date Part]=DATEPART(day, getdate())

-- 28

SELECT [Date Part]=DATEPART(dd, getdate())

SELECT [Date Part]=DATEPART(d, getdate())

SELECT [Date Part]=DATEPART(week, getdate())

-- 40

SELECT [Date Part]=DATEPART(wk, getdate())

SELECT [Date Part]=DATEPART(ww, getdate())

-- SQL day of week

SELECT [Date Part]=DATEPART(weekday, getdate())

-- 6

SELECT [Date Part]=DATEPART(dw, getdate())

SELECT [Date Part]=DATEPART(hour, getdate())

-- 5

SELECT [Date Part]=DATEPART(hh, getdate())

SELECT [Date Part]=DATEPART(minute, getdate())

-- 44

SELECT [Date Part]=DATEPART(mi, getdate())

SELECT [Date Part]=DATEPART(n, getdate())

SELECT [Date Part]=DATEPART(second, getdate())

-- 30

SELECT [Date Part]=DATEPART(ss, getdate())

SELECT [Date Part]=DATEPART(s, getdate())

SELECT [Date Part]=DATEPART(millisecond, getdate())

-- 590

SELECT [Date Part]=DATEPART(ms, getdate())

----------

----------

-- SQL DATENAME

----------

SELECT [Date Name]=DATENAME(Year, getdate())

-- 2012

SELECT [Date Name]=DATENAME(yy, getdate())

SELECT [Date Name]=DATENAME(yyyy, getdate())

SELECT [Date Name]=DATENAME(quarter, getdate())

-- 4

SELECT [Date Name]=DATENAME(qq, getdate())

SELECT [Date Name]=DATENAME(q, getdate())

-- SQL name of month

SELECT [Date Name]=DATENAME(month, getdate())

-- January

SELECT [Date Name]=DATENAME(mm, getdate())

SELECT [Date Name]=DATENAME(m, getdate())

-- SQL Julian date

SELECT [Date Name]=DATENAME(dayofyear, getdate())

-- 235

SELECT [Date Name]=DATENAME(dy, getdate())

SELECT [Date Name]=DATENAME(y, getdate())

-- SQL day of month

SELECT [Date Name]=DATENAME(day, getdate())

-- 18

SELECT [Date Name]=DATENAME(dd, getdate())

SELECT [Date Name]=DATENAME(d, getdate())

SELECT [Date Name]=DATENAME(week, getdate())

-- 30

SELECT [Date Name]=DATENAME(wk, getdate())

SELECT [Date Name]=DATENAME(ww, getdate())

-- SQL name of day

SELECT [Date Name]=DATENAME(weekday, getdate())

-- Saturday

SELECT [Date Name]=DATENAME(dw, getdate())

SELECT [Date Name]=DATENAME(hour, getdate())

-- 5

SELECT [Date Name]=DATENAME(hh, getdate())

SELECT [Date Name]=DATENAME(minute, getdate())

-- 44

SELECT [Date Name]=DATENAME(mi, getdate())

SELECT [Date Name]=DATENAME(n, getdate())

SELECT [Date Name]=DATENAME(second, getdate())

-- 30

SELECT [Date Name]=DATENAME(ss, getdate())

SELECT [Date Name]=DATENAME(s, getdate())

SELECT [Date Name]=DATENAME(millisecond, getdate())

-- 700

SELECT [Date Name]=DATENAME(ms, getdate())