SQL Convert Date Formats

As highlighted earlier, we might need to format a date in different formats as per our requirements. We can use the SQL CONVERT() function in SQL Server to format DateTime in various formats.

Syntax for the SQ: CONVERT() function is as follows.

1	SELECT CONVERT (data_type(length)),Date, DateFormatCode)

• Data_Type: We need to define data type along with length. In the date function, we use Varchar(length) data types
• Date: We need to specify the date that we want to convert
• DateFormatCode: We need to specify DateFormatCode to convert a date in an appropriate form. We will explore more on this in the upcoming section

Let us explore various date formats using SQL convert date functions.

First, we declare a variable to hold current DateTime using the SQL GETDATE() function with the following query.

123	declare @Existingdate datetimeSet @Existingdate=GETDATE()Print @Existingdate

We can see various date formats in the following table. You can keep this table handy for reference purpose in the format of Date Time columns.

Date and Time Formats	SQL convert date query	Output
Datetime format as MM/DD/YY Standard: U.S.A.	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,1) as [MM/DD/YY]
Datetime format in YY.MM.DD format Standard: ANSI	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,2) as [YY.MM.DD]
Datetime format in DD/MM/YY format Standard: British/French	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,3) as [DD/MM/YY]
Datetime format in DD.MM.YY formatStandard: German	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,4) as [DD.MM.YY]
Datetime format in DD-MM-YY format Standard: Italian	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,5) as [DD-MM-YY]
Datetime format in DD MMM YY format Standard: Shortened month name	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,6) as [DD MMM YY]
Datetime format in MMM DD, YY format Standard: Shortened month name	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,7) as [MMM DD,YY]
Datetime Format In HH:MM: SS Standard: 24 hour time	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,8) as [hh:mm:ss]
Datetime format as [MMM DD YYYY hh:mm:ss:mmm(AM/PM)] Standard: Default + milliseconds	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,9) as [MMM DD YYYY hh:mm:ss:mmm(AM/PM)]
Datetime format in MM-DD-YY format Standard: USA	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,10) as [MM-DD-YY]
Datetime format in YY/MM/DD format Standard: JAPAN	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,11) as [YY/MM/DD]
Datetime format in YYMMDD format Standard: ISO	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,12) as [YYMMDD]
Datetime format in DD MMM YYYY HH:MM:SS:MMM Standard: Europe default + milliseconds	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,13) as [DD MMM YYYY HH:MM:SS:MMM]
Datetime format in HH:MM:SS:MMM Standard:  24 hour time with milliseconds	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,14) as [HH:MM:SS:MMM]
Datetime format in YYYY-MM-DD HH:MM:SS Default: ODBC canonical	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,20) as [YYYY-MM-DD HH:MM:SS]
Datetime format in YYYY-MM-DD HH:MM:SS.mmm Standard: ODBC canonical with milliseconds	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,21) as [YYYY-MM-DD HH:MM:SS.mmm]
Datetime format in mm/dd/yy hh:mm:ss (AM/PM) Standard: USA with Time AM/PM	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,22) as [mm/dd/yy hh:mm:ss (AM/PM)]
Datetime format in [yyyy-mm-dd]	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,23) as [yyyy-mm-dd]
Datetime format in [hh:mm:ss]	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,24) as [hh:mm:ss]
Datetime format in [mm-dd-yyyy hh:mm:ss.mmm]	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,27) as [mm-dd-yyyy hh:mm:ss.mmm]
Datetime format in [MMM DD YYYY HH: SS (AM/PM)] Standard: Default	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,100) as [MMM DD YYYY HH:SS (AM/PM)]
Datetime format in [MM/DD/YYYY] Standard: USA	1234declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,101) as [MM/DD/YYYY]
Datetime format in [YYYY.MM.DD] Standard: ANSI	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,102) as [YYYY.MM.DD]
Datetime format in DD/MM/YYYY format Standard: British/French	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,103) as [DD/MM/YYYY]
Datetime format in DD.MM.YY format Standard: German	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,104) as [DD/MM/YYYY]
Datetime format in DD-MM-YY format Standard: Italian	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,105) as [DD/MM/YYYY]
Datetime format in DD MMM YYYY format Standard: Shortened month name	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,106) as [DD MMM YYYY]
Datetime format in MMM DD,YYYY format Standard: Shortened month name	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,107) as [MMM DD,YYYY]
Datetime Format In HH:MM: SS Standard: 24 hour time	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,108) as [HH:MM:SS]
Datetime format as [MMM DD YYYY hh:mm:ss:mmm(AM/PM)]Standard: Default + milliseconds	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,109) as [MMM DD YYYY hh:mm:ss:mmm(AM/PM)]
Datetime format in MM- DD-YY format Standard: USA	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,110) as [MM-DD-YYYY]
Datetime format in YYYY/MM/DD format Standard: JAPAN	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,111) as [YYYY/MM/DD]
Datetime format in YYYYMMDD format Standard: ISO	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,112) as [YYYYMMDD]
Datetime format in DD MMM YYYY HH:MM:SS: MMM Standard: Europe default + milliseconds	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,113) as [DD MMM YYYY HH:MM:SS:MMM]
Datetime format in HH:MM:SS: MMM Standard:  24 hour time with milliseconds	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,114) as [DD MMM YYYY HH:MM:SS:MMM]
Datetime format in YYYY-MM-DD HH:MM: SS Default: ODBC canonical	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,120) as [YYYY-MM-DD HH:MM:SS]
Datetime format in YYYY-MM-DD HH:MM: SS.mmm Standard: ODBC canonical with milliseconds	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,121) as [YYYY-MM-DD HH:MM:SS.mmm]
Datetime format in YYYY-MM-DDTHH:MM: SS.mmm Standard: ISO8601	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,126) as [yyyy-mm-ddThh:mi:ss.mmm]
Datetime format in [DD MMM YYYY hh:mi:ss:mmm(AM/PM)] Standard: Islamic/Hijri date	123declare @Existingdate datetimeSet @Existingdate=GETDATE()Select CONVERT(varchar,@Existingdate,130) as [dd mon yyyy hh:mi:ss:mmm(AM/PM)]