SQL Server DATE and TIME formats August 26th, 2013

Vinod Kumar

This topic is very close to my heart when I talk with customers almost daily. Customers when working in applications have a constant doubt of what format to use. There is no one right method, but here is one suggestion I have for you. Use one consistent format throughout your application especially when you plan to store inside SQL Server. Don’t try to mix and match formats – this is because whenever you need to compare dates, you will be doing an explicit CAST or CONVERT function in all your WHERE clauses. So in this blog, let me bring out the list of formats and the sample output for your reference.

Date Format

SQL Statement

Output

MM/DD/YY

CONVERT(VARCHAR(8), GETDATE(), 1)

09/29/12

YY.MM.DD

CONVERT(VARCHAR(8), GETDATE(), 2)

12.09.29

DD/MM/YY

CONVERT(VARCHAR(8), GETDATE(), 3)

29/09/12

DD.MM.YY

CONVERT(VARCHAR(8), GETDATE(), 4)

29.09.12

DD-MM-YY

CONVERT(VARCHAR(8), GETDATE(), 5)

29-09-12

DD Mon Y

CONVERT(VARCHAR(9), GETDATE(), 6)

29 Sep 12

Mon DD, Y

CONVERT(VARCHAR(10), GETDATE(), 7)

Sep 29, 12

MM-DD-YY

CONVERT(VARCHAR(8), GETDATE(), 10)

09-29-12

YY/MM/DD

CONVERT(VARCHAR(8), GETDATE(), 11)

12/09/29

YYMMDD

CONVERT(VARCHAR(6), GETDATE(), 12)

120929

Mon DD YYY

CONVERT(VARCHAR(20), GETDATE(), 100)

Sep 29 2012 9:51AM

MM/DD/YYYY

CONVERT(VARCHAR(10), GETDATE(), 101)

09/29/2012

YYYY.MM.DD

CONVERT(VARCHAR(10), GETDATE(), 102)

2012.09.29

DD/MM/YYYY

CONVERT(VARCHAR(10), GETDATE(), 103)

29/09/2012

DD.MM.YYYY

CONVERT(VARCHAR(10), GETDATE(), 104)

29.09.2012

DD-MM-YYYY

CONVERT(VARCHAR(10), GETDATE(), 105)

29-09-2012

DD Mon YYY

CONVERT(VARCHAR(11), GETDATE(), 106)

29 Sep 2012

Mon DD, YYYY

CONVERT(VARCHAR(12), GETDATE(), 107)

Sep 29, 2012

HH:MM:SS

CONVERT(VARCHAR(8), GETDATE(), 108)

09:51:05

Mon DD YYYY HH:MI:SS:MMMAM (or PM)

CONVERT(VARCHAR(26), GETDATE(), 109)

Sep 29 2012 9:51:05:910AM

MM-DD-YYYY

CONVERT(VARCHAR(10), GETDATE(), 110)

09-29-2012

YYYY/MM/DD

CONVERT(VARCHAR(10), GETDATE(), 111)

2012/09/29

YYYYMMDD

CONVERT(VARCHAR(8), GETDATE(), 112)

20120929

DD Mon YYYY HH: MM:SS:MMM(24h)

CONVERT(VARCHAR(24), GETDATE(), 113)

29 Sep 2012 09:51:05:910

HH:MI:SS:MMM(24H)

CONVERT(VARCHAR(12), GETDATE(), 114)

09:51:05:910

YYYY-MM-DD HH:MI:SS(24h)

CONVERT(VARCHAR(19), GETDATE(), 120)

2012-09-29 09:51:05

YYYY-MM-DD HH:MI:SS.MMM(24h)

CONVERT(VARCHAR(23), GETDATE(), 121)

2012-09-29 09:51:05.910

YYYY-MM-DDTHH:MM:SS:MMM

CONVERT(VARCHAR(23), GETDATE(), 126)

2012-09-29T09:51:05.910

DD/MM/YYYY HH:MI:SS:MMMAM

CONVERT(VARCHAR(25), GETDATE(), 131)

14/11/1433 9:51:05:910AM

Here are few more conversion functions for your reference.

Function Name

Description

Syntax

DATEFROMPARTS

Returns a DATE value for the specified year, month and day.

DATEFROMPARTS( <year>, <month>, <day> )

DATETIME2FROMPARTS

Returns a DATETIME2 value for the specified date and time and precision.

DATETIME2FROMPARTS( <year>, <month>, <day>, <hour>, <minute>, <seconds>, <fractions>, <precision> )

DATETIMEFROMPARTS

Returns a DATETIME value for the date and time.

DATETIMEFROMPARTS( <year>, <month>, <day>, <hour>, <minute>, <seconds>, <milliseconds> )

DATETIMEOFFSETFROMPARTS

Returns a DATETIMEOFFSET value for the date and time and with the offsets and precision.

DATETIMEOFFSETFROMPARTS( <year>, <month>, <day>, <hour>, <minute>, <seconds>, <fractions>, <hour_offset>, <minute_offset>, <precision> )

SMALLDATETIMEFROMPARTS

Returns a SMALLDATETIME value for the date and time.

SMALLDATETIMEFROMPARTS( <year>, <month>, <day>, <hour>, <minute> )

TIMEFROMPARTS

Returns a TIME value for the time and with the specified precision.

TIMEFROMPARTS( <hour>, <minute>, <seconds>, <fractions>, <precision> )

EOMONTH

Returns last day of the month for the date.

EOMONTH( <start_date> [, <month_to_add>] )

  1. <year> – Integer specifying a year.
  2. <month> – Integer specifying a month.
  3. <day> – Integer specifying a day.
  4. <hour> – Integer specifying hours.
  5. <minute> – Integer specifying minutes.
  6. <seconds> – Integer specifying seconds.
  7. <milliseconds> – Integer specifying milliseconds.
  8. <fractions> – Integer specifying fractions. This parameter depends on the <precision> parameter.
  9. <hour_offset> – Integer specifying the hour portion of the time zone offset.
  10. <minute_offset> – Integer specifying the minute portion of the time zone offset.
  11. <precision> – Integer specifying the precision of the DATETIME2 value to be returned. For example, if <precision> is 7, then each fraction represents 100 nanoseconds.
  12. <start_date> – Date for which to return the last day of the month.
  13. <month_to_add> – Integer specifying the number of months to add to <start_date>.

Hope this will be a good start and reference when it comes to using date and time. Is there anything else that you have done more with Date functions? Do let us know.

Tags: , , , , , , , ,

This entry was posted on Monday, August 26th, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply