SQL Convert Date functions and formats (2024)

Table of Contents
Data Types for Date and Time SQL Convert Date Formats DATEADD Conclusion Related posts: References

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. 1

    Select SYSDATETIME() as [SYSDATETIME]

  2. 1

    Select SYSDATETIMEOffset() as [SYSDATETIMEOffset]

  3. 1

    Select GETUTCDATE() as [GETUTCDATE]

  4. 1

    Select GETDATE() as [GETDATE]

SQL Convert Date functions and formats (1)

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.

See Also
AMD Radeon RX 590 GME SpecsPolaris Bios Editor 3 PRO 2022 - PBE 3 Pro Performance Timings2018 Polaris Ranger 4x4 Utility Vehicle

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.

1

2

3

declare @Existingdate datetime

Set @Existingdate=GETDATE()

Print @Existingdate

SQL Convert Date functions and formats (2)

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.

1

2

3

declare @Existingdate datetime

Set @Existingdate=GETDATE()

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


Datetime format in
YY.MM.DD format

Standard: ANSI

1

2

3

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

1

2

3

declare @Existingdate datetime

Set @Existingdate=GETDATE()

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



Datetime format in DD.MM.YY format

Standard: German

1

2

3

declare @Existingdate datetime

Set @Existingdate=GETDATE()

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


Datetime format in
DD-MM-YY format

Standard: Italian

1

2

3

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

1

2

3

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

1

2

3

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

1

2

3

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

1

2

3

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

1

2

3

declare @Existingdate datetime

Set @Existingdate=GETDATE()

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


Datetime format in
YY/MM/DD format

Standard: JAPAN

1

2

3

declare @Existingdate datetime

Set @Existingdate=GETDATE()

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


Datetime format in
YYMMDD format

Standard: ISO

1

2

3

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

1

2

3

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

1

2

3

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

1

2

3

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

1

2

3

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

1

2

3

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]

1

2

3

declare @Existingdate datetime

Set @Existingdate=GETDATE()

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



Datetime format in
[hh:mm:ss]

1

2

3

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]

1

2

3

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

1

2

3

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

1

2

3

4

declare @Existingdate datetime

Set @Existingdate=GETDATE()

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


Datetime format in
[YYYY.MM.DD]

Standard: ANSI

1

2

3

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

1

2

3

declare @Existingdate datetime

Set @Existingdate=GETDATE()

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


Datetime format in
DD.MM.YY format

Standard: German

1

2

3

declare @Existingdate datetime

Set @Existingdate=GETDATE()

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


Datetime format in
DD-MM-YY format

Standard: Italian

1

2

3

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

1

2

3

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

1

2

3

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

1

2

3

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

1

2

3

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

1

2

3

declare @Existingdate datetime

Set @Existingdate=GETDATE()

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


Datetime format in
YYYY/MM/DD format

Standard: JAPAN

1

2

3

declare @Existingdate datetime

Set @Existingdate=GETDATE()

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


Datetime format in
YYYYMMDD format

Standard: ISO

1

2

3

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

1

2

3

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

1

2

3

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

1

2

3

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

1

2

3

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

1

2

3

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

1

2

3

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

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.

1

2

3

SELECT GETDATE() as Currentdate

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

You can see the output in the following screenshot.

SQL Convert Date functions and formats (42)

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

1

2

3

4

select GETDATE() as Currentdate

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

SQL Convert Date functions and formats (43)

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.

1

2

3

SELECT

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

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

SQL Convert Date functions and formats (44)

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)

  • How to install PostgreSQL on Ubuntu - July 13, 2023
  • How to use the CROSSTAB function in PostgreSQL - February 17, 2023
  • Learn the PostgreSQL COALESCE command - January 19, 2023

Related posts:

  1. Funciones y formatos de SQL Convert Date
  2. A comprehensive guide to the SQL Format function
  3. SQL Convert Function
  4. SQL Server and BI – Creating a query for the revenue projection
  5. Top SQL Server Books
SQL Convert Date functions and formats (2024)

References

Top Articles
UNCA will award 3 honorary doctorates at 2024 commencement: Who are the recipients?
Asheville leaders push for change on how tourism funds are used. Will locals get a bigger cut?
Judge Nelly N Khouzam Political Party
Desi Cinemas.com
What Is Vanderpump Rules? Everything to Know About the Bravo Series | Bravo TV Official Site
'Vanderpump Rules' recap: It's time for a TomTom party, but things really go bad for Jax
Slay the Spire: Die besten Tipps für Anfänger und Fortgeschrittene
Slay the Spire war super, aber das Downfall-Addon ist die Krönung
Ginger Jeffries Left Kusi
Items - Remove the single pylon restriction from the Universal Pylon
En eaux très troubles
The Meg 3 Release, Cast & Everything We Know
Latest Posts
Les JT de France 2, France 3 et franceinfo : JT en direct et replay
Search Results - Obituaries published on Brandon Sun Passages
Article information

Author: Fredrick Kertzmann

Last Updated:

Views: 5477

Rating: 4.6 / 5 (46 voted)

Reviews: 93% of readers found this page helpful

Author information

Name: Fredrick Kertzmann

Birthday: 2000-04-29

Address: Apt. 203 613 Huels Gateway, Ralphtown, LA 40204

Phone: +2135150832870

Job: Regional Design Producer

Hobby: Nordic skating, Lacemaking, Mountain biking, Rowing, Gardening, Water sports, role-playing games

Introduction: My name is Fredrick Kertzmann, I am a gleaming, encouraging, inexpensive, thankful, tender, quaint, precious person who loves writing and wants to share my knowledge and understanding with you.