SQL Server Date Format

SQL server is used widely all over the world. In different country and different places, date format are different. For example in India it is used day month year format (31/03/2018) but in US it is used month day year(03/31/2018) format. When SQL server returned results, developer have to write code in such a way, that date format should be compatible for that country and region. 

         SQL Server have no such regular expression to express the date format.However , there is some some indicator that return date in a specific style format. Below is the example. This example so how the number 100-113  return different format of dates and Time. This format does not return date only, it is a combination of date and time. You have to  subtract date part  of the output.Bellow  how the extraction is done to extract date part.


    101    mm/dd/yyyy    US
    102    yyyy.mm.dd    ANSI
    103    dd/mm/yyyy    British/French
    104    dd.mm.yyyy    German
    105    dd-mm-yyyy    Italian
    106    dd mon yyyy   -
    107    Mon dd, yyyy  -
    108    hh:mm:ss      -
    109    mon dd yyyy   hh:mi:ss:mmmAM 
    110    mm-dd-yyyy    USA
    111    yyyy/mm/dd    Japan
    112    yyyymmdd      ISO
    113    dd mon yyyy hh:mi:ss:mmm    Europe (24 hour clock)>
     
Todays is 20th August 2018.I am creating the article.Now sql server return the following code.

Example

SELECT CONVERT(varchar, GETDATE(), 100)

Output : Aug 20 2018 12:01PM

 

SELECT CONVERT(varchar, GETDATE(), 101)

Output : 08/20/2018

 

SELECT CONVERT(varchar, GETDATE(), 102)

Output : 2018.08.20

 

SELECT CONVERT(varchar, GETDATE(), 103)

Output : 20/08/2018

 

SELECT CONVERT(varchar, GETDATE(), 104)

Output : 20.08.2018

 

SELECT CONVERT(varchar, GETDATE(), 105)

Output : 20-08-2018

SELECT CONVERT(varchar, GETDATE(), 106)

Output : 20 Aug 2018

SELECT CONVERT(varchar, GETDATE(), 107)

Output : Aug 20, 2018

SELECT CONVERT(varchar, GETDATE(), 108)

Output : 12:02:59

SELECT CONVERT(varchar, GETDATE(), 109)

Output :  Aug 20 2018 12:03:12:070PM

SELECT CONVERT(varchar, GETDATE(), 110)

Output : 08-20-2018

SELECT CONVERT(varchar, GETDATE(), 111)

Output : 2018/08/20

SELECT CONVERT(varchar, GETDATE(), 112)

Output : 20180820

SELECT CONVERT(varchar, GETDATE(), 113)

Output : 20 Aug 2018 12:03:40:220 

Below are list of more date format available in Sql Server

    1    mm/dd/yy   

    2    yy.mm.dd

    3     yy.mm.dd

    4    dd/mm/yyyy

    5    dd-mm-yy  

    6    dd MM yy   

    7    MM dd yy

    8    Time   

    9     MM dd yyyy +Time   

    10    dd/mm   

    11   mm/dd/yyyy   

    12    yymmdd   

    13    dd MM yyyy +Time  

    14   mm/dd/yyyy    

Example

 

SELECT CONVERT(varchar, GETDATE(), 1) 

Output : 08/20/18

SELECT CONVERT(varchar, GETDATE(), 2)

Output : 18.08.20

SELECT CONVERT(varchar, GETDATE(), 3) 

Output : 20/08/18

 dd/mm/yy

SELECT CONVERT(varchar, GETDATE(), 4)

Output : 20.08.18

 dd.mm.yy

SELECT CONVERT(varchar, GETDATE(), 5)

Output : 20-08-18

dd-mm-yy

SELECT CONVERT(varchar, GETDATE(), 6)

Output : 20 Aug 18

dd MM yy

SELECT CONVERT(varchar, GETDATE(), 7)

Output : Aug 20, 18

MM dd yy

SELECT CONVERT(varchar, GETDATE(), 8)

Output :  13:20:29

SELECT CONVERT(varchar, GETDATE(), 9) 

Output : Aug 20 2018 1:20:46:377PM

MM dd yyyy +Time

SELECT CONVERT(varchar, GETDATE(), 10) 

Output : 08-20-18

mm-dd-yy

SELECT CONVERT(varchar, GETDATE(), 11)

Output :  18/08/20

 dd/mm

SELECT CONVERT(varchar, GETDATE(), 12) 

Output : 180820

yymmdd

SELECT CONVERT(varchar, GETDATE(), 13)

Output :  20 Aug 2018 13:21:32:553

dd MM yyyy +Time

SELECT CONVERT(varchar, GETDATE(), 14)

Output :  13:21:32:553