Different DATETIME Formats in SQL

Hi all  I came back with  an article of DATETIME datatype formats. I came across a scenario so that I need to use the date time in different  styles.

Date and time values can be stored with either the DATETIME or  SMALLDATETIME data type. The difference between the two is that SMALLDATETIME supports a smaller range of dates and does not give the same level of precision when accounting for time. DATETIME data type can hold the  values from January 1st of 1753 to December 31st of 9999.

SQL Server 2005 does not have the data types that stores only Date or only Time. In SQL Server 2008, there are new data types, including TIME, DATE, DATETIME2 and DATETIMEOFFSET. This helps us to store the date and time separately. If that is the case, you will have to resort to storing date and time portions in a VARCHAR or CHAR column (in earlier versions of SQL Server) or upgrade to SQL Server 2008 to use the new data types.

SQL Server allows for a multitude of date and time formats. Some of these are described in the following table.

Without century (2 digit year)	With century (4 digit year)	Standard	Input/Output Format
-	0 or 100	Default	mon dd yyyy hh:miAM (or PM)
1	101	USA	mm/dd/yy
2	102	ANSI	yy.mm.dd
3	103	British/French	dd/mm/yy
4	104	German	dd.mm.yy
5	105	Italian	dd-mm-yy
6	106	-	dd mon yy
7	107	-	Mon dd, yy
8	108	-	hh:mm:ss
-	9 or 109	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	USA	mm-dd-yy
11	111	JAPAN	yy/mm/dd
12	112	ISO	dd mon yyyy hh:mm:ss:mmm(24h)
-	13 or 113	Europe default + milliseconds	yymmdd
14	114	-	hh:mi:ss:mmm(24h)
-	20 or 120	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
-	21 or 121	ODBC canonical (with milliseconds)	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126	ISO8601	yyyy-mm-dd Thh:mm:ss:mmm(no spaces)
-	130	Kuwaiti	dd mon yyyy hh:mi:ss:mmmAM
-	131	Kuwaiti	dd/mm/yy hh:mi:ss:mmmAM

        There are a couple of other issues with date handling in SQL Server. First of all, be aware of international matters. Different countries start weeks on different days. You can check this setting by examining the value of the @@DATEFIRST global variable. Use SET DATEFIRST command to change the first day of the week as follows:

1.SET DATEFIRST n

Where n is the day number, 1 being Monday and 7 being Sunday.

The date format can be easily configured by using SET DATEFORMAT as follows:

1.SET DATEFORMAT format

Allowable formats include dmy (day, month, year), mdy (month, day, year), ymd (year, month, day), ydm (year, day,month), myd (month, year, day), and dym (day, year, month).

Date and Time Format Examples

This returns date in a British format:

1.SELECT CONVERT(VARCHAR(12), CAST('5/3/2001' AS datetime), 103)

Result:

1.------------ 

2.03/05/2001
This returns the current date in American format:

1.SELECT CONVERT(VARCHAR(12), GETDATE(), 101)

  Result:

1.------------  

2.05/03/2001

This returns the date in German format:

1.SELECT CONVERT(VARCHAR(12), CAST('5/3/2001' AS SMALLDATETIME), 104)

 Result: 

1.------------  

2.03.05.2001