SQL Convert Date functions and formats

In this article, we will explore various SQL Convert Date formats to use in writing SQL queries.

We need to work with date type data in SQL. It can be a complicated thing to deal with, at times, for SQL Server developers. Suppose you have a Product table with a column timestamp. It creates a timestamp for each customer order. You might face the following issues with it

You fail to insert data in the Product table because the application tries to insert data in a different date format
Suppose you have data in a table in the format YYYY-MM-DD hh:mm: ss. You have a daily Sales report, and in that, you want data group by date. You want to have data in the report in format YYYY-MM-DD

We do face many such scenarios when we do not have a date format as per our requirement. We cannot change table properties to satisfy each requirement. In this case, we need to use the built-in functions in SQL Server to give the required date format.

Data Types for Date and Time

We have the following SQL convert date and Time data types in SQL Server.

Date type	Format
Time	hh:mm:ss[.nnnnnnn]
Date	YYYY-MM-DD
SmallDateTime	YYYY-MM-DD hh:mm:ss
DateTime	YYYY-MM-DD hh:mm:ss[.nnn]
DateTime2	YYYY-MM-DD hh:mm:ss[.nnnnnnn]
DateTimeOffset	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+\|-]hh:mm

In SQL Server, we have used built-in functions such as SQL GETDATE() and GetUTCDate() to provide server date and format in various formats.

SYSDATETIME(): To returns the server’s date and time
SYSDATETIMEOffset(): It returns the server’s date and time, along with UTC offset
GETUTCDATE(): It returns date and GMT (Greenwich Mean Time ) time
GETDATE(): It returns server date and time

Execute the following queries to get output in respective formats.

1

Select SYSDATETIME() as [SYSDATETIME]
1

Select SYSDATETIMEOffset() as [SYSDATETIMEOffset]
1

Select GETUTCDATE() as [GETUTCDATE]
1

Select GETDATE() as [GETDATE]

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.

declare @Existingdate datetime

Set @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.

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,1) as [MM/DD/YY]

Datetime format in
YY.MM.DD format

Standard: ANSI

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,2) as [YY.MM.DD]

Datetime format in
DD/MM/YY format

Standard: British/French

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,3) as [DD/MM/YY]

Datetime format in DD.MM.YY format

Standard: German

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,4) as [DD.MM.YY]

Datetime format in
DD-MM-YY format

Standard: Italian

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,5) as [DD-MM-YY]

Datetime format in
DD MMM YY format

Standard: Shortened month name

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,6) as [DD MMM YY]

Datetime format in
MMM DD, YY format

Standard: Shortened month name

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,7) as [MMM DD,YY]

Datetime Format
In HH:MM: SS

Standard: 24 hour time

declare @Existingdate datetime

Set @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

declare @Existingdate datetime

Set @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

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,10) as [MM-DD-YY]

Datetime format in
YY/MM/DD format

Standard: JAPAN

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,11) as [YY/MM/DD]

Datetime format in
YYMMDD format

Standard: ISO

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,12) as [YYMMDD]

Datetime format in
DD MMM YYYY HH:MM:SS:MMM

Standard: Europe default + milliseconds

declare @Existingdate datetime

Set @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

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,14) as [HH:MM:SS:MMM]

Datetime format in
YYYY-MM-DD HH:MM:SS

Default: ODBC canonical

declare @Existingdate datetime

Set @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

declare @Existingdate datetime

Set @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

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,22) as [mm/dd/yy hh:mm:ss (AM/PM)]

Datetime format in
[yyyy-mm-dd]

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,23) as [yyyy-mm-dd]

Datetime format in
[hh:mm:ss]

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,24) as [hh:mm:ss]

Datetime format in
[mm-dd-yyyy hh:mm:ss.mmm]

declare @Existingdate datetime

Set @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

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,100) as [MMM DD YYYY HH:SS (AM/PM)]

Datetime format in
[MM/DD/YYYY]

Standard: USA

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,101) as [MM/DD/YYYY]

Datetime format in
[YYYY.MM.DD]

Standard: ANSI

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,102) as [YYYY.MM.DD]

Datetime format in
DD/MM/YYYY format

Standard: British/French

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,103) as [DD/MM/YYYY]

Datetime format in
DD.MM.YY format

Standard: German

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,104) as [DD/MM/YYYY]

Datetime format in
DD-MM-YY format

Standard: Italian

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,105) as [DD/MM/YYYY]

Datetime format in
DD MMM YYYY format

Standard: Shortened month name

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,106) as [DD MMM YYYY]

Datetime format in
MMM DD,YYYY format

Standard: Shortened month name

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,107) as [MMM DD,YYYY]

Datetime Format
In HH:MM: SS

Standard: 24 hour time

declare @Existingdate datetime

Set @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

declare @Existingdate datetime

Set @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

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,110) as [MM-DD-YYYY]

Datetime format in
YYYY/MM/DD format

Standard: JAPAN

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,111) as [YYYY/MM/DD]

Datetime format in
YYYYMMDD format

Standard: ISO

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,112) as [YYYYMMDD]

Datetime format in
DD MMM YYYY HH:MM:SS: MMM

Standard: Europe default + milliseconds

declare @Existingdate datetime

Set @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

declare @Existingdate datetime

Set @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

declare @Existingdate datetime

Set @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

declare @Existingdate datetime

Set @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

declare @Existingdate datetime

Set @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

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Select CONVERT(varchar,@Existingdate,130) as [dd mon yyyy hh:mi:ss:mmm(AM/PM)]

In the table, we can see various formats to SQL convert date as per your requirements. In the following table, you can see all SQL date formats together.

Date format option	SQL convert date output
0	Dec 30 2006 12:38AM
1	12/30/06
2	06.12.30
3	30/12/2006
4	30.12.06
5	30/12/2006
6	30-Dec-06
7	Dec 30, 06
8	00:38:54
9	Dec 30 2006 12:38:54:840AM
10	12-30-06
11	06/12/1930
12	61230
13	30 Dec 2006 00:38:54:840
14	00:38:54:840
20	30/12/2006 00:38
21	38:54.8
22	12/30/06 12:38:54 AM
23	30/12/2006
24	00:38:54
25	38:54.8
26	2006-30-12 00:38:54.840
27	12-30-2006 00:38:54.840
28	12-2006-30 00:38:54.840
29	38:54.8
30	30-2006-12 00:38:54.840
31	2006-30-12
32	12-30-2006
33	12-2006-30
34	30/12/2006
35	30-2006-12
100	Dec 30 2006 12:38AM
101	12/30/2006
102	2006.12.30
103	30/12/2006
104	30.12.2006
105	30/12/2006
106	30-Dec-06
107	Dec 30, 2006
108	00:38:54
109	Dec 30 2006 12:38:54:840AM
110	12-30-2006
111	30/12/2006
112	20061230
113	30 Dec 2006 00:38:54:840
114	00:38:54:840
120	30/12/2006 00:38
121	38:54.8
126	2006-12-30T00:38:54.840
127	2006-12-30T00:38:54.840
130	10 ?? ????? 1427 12:38:54:840A
131	10/12/1427 12:38:54:840AM

Let us next explore a function that is useful for SQL convert date.

DATEADD

We can use the SQL DATEADD function to add a particular period to our date. Suppose we have a requirement to add 1 month to current date. We can use the SQL DATEADD function to do this task.

The syntax for SQL DATEADD function is as following

1	DATEADD(interval, number, date)

Interval: We can specify an interval that needs to be added in the specified date. We can have values such as year, quarter, month, day, week, hour, minute etc.

Number: It specifies the number of the interval to add. For example, if we have specified interval as Month and Number as 2, it means 2 months needs to be added in date.

In the following query, we want to add 2 months in the current date.

SELECT GETDATE() as Currentdate

SELECT DATEADD(month, 2, GETDATE()) AS NewDate;

You can see the output in the following screenshot.

Similarly, lets us add 1 year to current date using the following query.

select GETDATE() as Currentdate

SELECT DATEADD(Year, 1, GETDATE()) AS NewDate;

We can combine the SQL DATEADD and CONVERT functions to get output in desired DateTime formats. Suppose, in the previous example; we want a date format in of MMM DD, YYYY. We can use the format code 107 to get output in this format.

Execute the following code to get New date and ConvertedDate.

SELECT

DATEADD(YEAR,1,GETDATE()) AS [NewDate]

,CONVERT(varchar(110),DATEADD(YEAR,1,GETDATE()),107) AS [ConvertedDate]

Conclusion

In this article, we explored various SQL convert date formats. It allows getting a date in required format with Covert function easily. You can use this article to take a reference for all date formats and use in your queries.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023

Data Types for Date and Time

SQL Convert Date Formats

DATEADD

Conclusion

Related posts: