All SQL date formats

Hi,

In this Post I will list all the SQL date formats which we generally use while writing the SQL queries,

The most frequently asked questions in SQL Server forums is how to format a datetime value or column into a specific date format. So here is a list of different date formats that come standard in SQL Server as part of the CONVERT function.

 

Type Code Format Code

100

Apr 16 2013 11:01AM SELECT convert(varchar,getdate(),100)

101

4/16/2013

SELECT convert(varchar,getdate(),101)

102

2013.04.16 SELECT convert(varchar,getdate(),102)

103

16/04/2013 SELECT convert(varchar,getdate(),103)

104

16.04.2013 SELECT convert(varchar,getdate(),104)

105

16-04-2013 SELECT convert(varchar,getdate(),105)

106

16-Apr-13

SELECT convert(varchar,getdate(),106)

107

16-Apr-13

SELECT convert(varchar,getdate(),107)

108

11:01:54

SELECT convert(varchar,getdate(),108)

109

Apr 16 2013 11:01:54:223AM SELECT convert(varchar,getdate(),109)

110

4/16/2013

SELECT convert(varchar,getdate(),110)

111

4/16/2013

SELECT convert(varchar,getdate(),111)

112

20130416

SELECT convert(varchar,getdate(),112)

113

16 Apr 2013 11:01:54:223 SELECT convert(varchar,getdate(),113)

114

11:01:54:223 SELECT convert(varchar,getdate(),114)

 

Please copy the below code and execute in SQL management student you will get the above information

select ‘100’ TypeCode,convert(varchar,getdate(),100) Format, ‘SELECT convert(varchar,getdate(),100)’ Code UNION

select ‘101’ TypeCode,convert(varchar,getdate(),101) Format, ‘SELECT convert(varchar,getdate(),101)’ Code UNION

select ‘102’ TypeCode,convert(varchar,getdate(),102) Format, ‘SELECT convert(varchar,getdate(),102)’ Code UNION

select ‘103’ TypeCode,convert(varchar,getdate(),103) Format, ‘SELECT convert(varchar,getdate(),103)’ Code UNION

select ‘104’ TypeCode,convert(varchar,getdate(),104) Format, ‘SELECT convert(varchar,getdate(),104)’ Code UNION

select ‘105’ TypeCode,convert(varchar,getdate(),105) Format, ‘SELECT convert(varchar,getdate(),105)’ Code UNION

select ‘106’ TypeCode,convert(varchar,getdate(),106) Format, ‘SELECT convert(varchar,getdate(),106)’ Code UNION

select ‘107’ TypeCode,convert(varchar,getdate(),107) Format, ‘SELECT convert(varchar,getdate(),107)’ Code UNION

select ‘108’ TypeCode,convert(varchar,getdate(),108) Format, ‘SELECT convert(varchar,getdate(),108)’ Code UNION

select ‘109’ TypeCode,convert(varchar,getdate(),109) Format, ‘SELECT convert(varchar,getdate(),109)’ Code UNION

select ‘110’ TypeCode,convert(varchar,getdate(),110) Format, ‘SELECT convert(varchar,getdate(),110)’ Code UNION

select ‘111’ TypeCode,convert(varchar,getdate(),111) Format, ‘SELECT convert(varchar,getdate(),111)’ Code UNION

select ‘112’ TypeCode,convert(varchar,getdate(),112) Format, ‘SELECT convert(varchar,getdate(),112)’ Code UNION

select ‘113’ TypeCode,convert(varchar,getdate(),113) Format, ‘SELECT convert(varchar,getdate(),113)’ Code UNION

select ‘114’ TypeCode,convert(varchar,getdate(),114) Format, ‘SELECT convert(varchar,getdate(),114)’ Code

Please do Comment to the posts..

Thank You….

Leave a Reply