Rajendra Gupta
SQL Convert Date - SQL Date functions

SQL Convert Date Functions and Formats

April 3, 2019 by

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

SQL Convert Date - SQL Date functions

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.

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

SQL Convert Date - SQL Date functions

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.


SQL Convert Date - date format MM/DD/YY


Datetime format in
YY.MM.DD format

Standard: ANSI


Datetime format in YY.MM.DD format


Datetime format in
DD/MM/YY format

Standard: British/French


Datetime format in DD/MM/YY format


Datetime format in DD.MM.YY format

Standard: German


Datetime format in DD.MM.YY format


Datetime format in
DD-MM-YY format

Standard: Italian


Datetime format in DD-MM-YY format


Datetime format in
DD MMM YY format

Standard: Shortened month name


Datetime format in DD MMM YY format


Datetime format in
MMM DD, YY format

Standard: Shortened month name


Datetime format in MMM DD,YY format


Datetime Format
In HH:MM: SS

Standard: 24 hour time


SQL Convert Date - Datetime Format
In HH:MM:SS


Datetime format as
[MMM DD YYYY hh:mm:ss:mmm(AM/PM)]

Standard: Default + milliseconds


Datetime format as 
[MMM DD YYYY hh:mm:ss:mmm(AM/PM)]


Datetime format in
MM-DD-YY format

Standard: USA


Datetime format in MM- DD-YY format


Datetime format in
YY/MM/DD format

Standard: JAPAN


Datetime format in YY/MM/DD format


Datetime format in
YYMMDD format

Standard: ISO


Datetime format in YYMMDD format


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

Standard: Europe default + milliseconds


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


Datetime format in
HH:MM:SS:MMM

Standard:  24 hour time with milliseconds


Datetime format in HH:MM:SS: MMM


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

Default: ODBC canonical


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


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

Standard: ODBC canonical with milliseconds


SQL Convert Date - Datetime format in YYYY-MM-DD HH:MM: SS.mmm


Datetime format in
mm/dd/yy hh:mm:ss (AM/PM)

Standard: USA with Time AM/PM


Datetime format in mm/dd/yy hh:mm:ss (AM/PM)


Datetime format in
[yyyy-mm-dd]


Datetime format in [yyyy-mm-dd]


Datetime format in
[hh:mm:ss]


Datetime format in [hh:mm:ss]


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


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


Datetime format in
[MMM DD YYYY HH: SS (AM/PM)]

Standard: Default


Datetime format in [MMM DD YYYY HH: SS (AM/PM)]


Datetime format in
[MM/DD/YYYY]

Standard: USA


Datetime format in [MM/DD/YYYY]


Datetime format in
[YYYY.MM.DD]

Standard: ANSI


Datetime format in [YYYY.MM.DD]


Datetime format in
DD/MM/YYYY format

Standard: British/French


Datetime format in DD/MM/YYYY format


Datetime format in
DD.MM.YY format

Standard: German


Datetime format in DD.MM.YY format


Datetime format in
DD-MM-YY format

Standard: Italian


Datetime format in DD-MM-YY format


Datetime format in
DD MMM YYYY format

Standard: Shortened month name


Datetime format in DD MMM YYYY format


Datetime format in
MMM DD,YYYY format

Standard: Shortened month name


Datetime format in MMM DD,YYYY format


Datetime Format
In HH:MM: SS

Standard: 24 hour time


SQL Convert Date -  Datetime Format
In HH:MM: SS


Datetime format as
[MMM DD YYYY hh:mm:ss:mmm(AM/PM)]

Standard: Default + milliseconds


Datetime format as 
[MMM DD YYYY hh:mm:ss:mmm(AM/PM)]


Datetime format in
MM- DD-YY format

Standard: USA


Datetime format in MM- DD-YY format


Datetime format in
YYYY/MM/DD format

Standard: JAPAN


Datetime format in YYYY/MM/DD format


Datetime format in
YYYYMMDD format

Standard: ISO


Datetime format in YYYYMMDD format


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

Standard: Europe default + milliseconds


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


Datetime format in
HH:MM:SS: MMM

Standard:  24 hour time with milliseconds


Datetime format in HH:MM:SS: MMM


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

Default: ODBC canonical


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


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

Standard: ODBC canonical with milliseconds


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


Datetime format in
YYYY-MM-DDTHH:MM: SS.mmm

Standard: ISO8601


Datetime format in YYYY-MM-DDTHH:MM: SS.mmm


Datetime format in
[DD MMM YYYY hh:mi:ss:mmm(AM/PM)]

Standard: Islamic/Hijri date


SQL Convert Date - Datetime format in [DD MMM 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

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.

You can see the output in the following screenshot.

DateADD function SQL -  - SQL Convert Date

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

SQL convert date - DateADD function SQL

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.

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.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

861 Views