SQL Date Format and Date Functions in SQL

The most difficult part when working with dates in an SQL server be sure that the format of the date we are trying to insert, matches the format of the date column in the database.

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

You 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 we have a BillingTransection table with a column timestamp. It creates a timestamp for each guest order. You might face the following issues with it.

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

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

Data Types for Date and Time in SQL

SQL Server comes with the given below data types for storing a date or a date/time value in the database:

DATE - format YYYY-MM-DD
Time - format hh:mm:ss[.nnnnnnn]
DATETIME - format: YYYY-MM-DD HH:MI:SS
SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: a unique number
DateTime2 -YYYY-MM-DD hh:mm:ss[.nnnnnnn]
DateTimeOffset -format YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm

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

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

Execute the given below queries to get output in respective formats

Select SYSDATETIME() as [SYSDATETIME]

Select SYSDATETIMEOffset() as [SYSDATETIMEOffset]

Select GETUTCDATE() as [GETUTCDATE]

Select GETDATE() as [GETDATE]

SQL Convert Date Formats

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

The syntax for the SQL: CONVERT() function is as given below.

SELECT CONVERT (DataType(length)),Date, DateFormatCode)

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

First, You declare a variable to hold the current DateTime using the SQL GETDATE() function with the given below query.

DECLARE @DemoDate DATETIME

SET @DemoDate=GETDATE()

PRINT @DemoDate

You can see various date formats in the given table. You can keep this table handy for reference purposes 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 @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,1) AS [MM/DD/YY]

MM/DD/YY
04/23/22

Datetime format in
YY.MM.DD format
Standard: ANSI

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,2) AS [YY.MM.DD]

YY.MM.DD
22.04.23

Datetime format in
DD/MM/YY format
Standard: British/French

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,3) AS [DD/MM/YY]

DD/MM/YY
23/04/22

Datetime format in

DD.MM.YY format
Standard: German

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,4) AS [DD.MM.YY]

DD.MM.YY
23.04.22

Datetime format in
DD-MM-YY format
Standard: Italian

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,5) AS [DD-MM-YY]

DD-MM-YY
23-04-22

Datetime format in
DD MMM YY format
Standard: Shortened month name

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,6) AS [DD MMM YY]

DD MMM YY
23 Apr 22

Datetime format in
MMM DD, YY format
Standard: Shortened month name

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,7) AS [MMM DD,YY]

MMM DD,YY
Apr 23, 22

Datetime Format
In HH:MM: SS
Standard: 24hour time

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,8) AS [hh:mm:ss]

hh:mm:ss
14:40:29

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

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,9) AS [MMM DD YYYY hh:mm:ss:mmm(AM/PM)]

MMM DD YYYY hh:mm:ss:mmm(AM/PM)
Apr 23 2022 2:40:50:597PM

Datetime format in
MM-DD-YY format
Standard: USA

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,10) AS [MM-DD-YY]

MM-DD-YY
04-23-22

Datetime format in
YY/MM/DD format
Standard: JAPAN

DECLARE @DemoDate DATETIME
SET @DemoDate =GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate ,11) AS [YY/MM/DD]

YY/MM/DD
22/04/23

Datetime format in
YYMMDD format
Standard: ISO

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,12) AS [YYMMDD]

YYMMDD
220423

Datetime format in
DD MMM YYYY HH:MM:SS:MMM
Standard: Europe default + milliseconds

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,13) AS [DD MMM YYYY HH:MM:SS:MMM]

DD MMM YYYY HH:MM:SS:MMM
23 Apr 2022 14:47:59:343

Datetime format in
HH:MM:SS:MMM
Standard: 24 hour time with milliseconds

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,14) AS [HH:MM:SS:MMM]

HH:MM:SS:MMM
14:49:19:933

Datetime format in
YYYY-MM-DD HH:MM:SS
Default: ODBC canonical

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,20) AS [YYYY-MM-DD HH:MM:SS]

HH:MM:SS:MMM
14:49:19:933

Datetime format in
YYYY-MM-DD HH:MM:SS.mmm
Standard: ODBC canonical with milliseconds

DECLARE @DemoDate DATETIME
SET @DemoDate =GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate ,21) AS [YYYY-MM-DD HH:MM:SS.mmm]

YYYY-MM-DD HH:MM:SS.mmm
2022-04-23 14:53:44.047

Datetime format in
mm/dd/yy hh:mm:ss (AM/PM)
Standard: USA with Time AM/PM

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,22) AS [mm/dd/yy hh:mm:ss (AM/PM)]

mm/dd/yy hh:mm:ss (AM/PM)
04/23/22 3:01:46 PM

Datetime format in
[yyyy-mm-dd]

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,23) AS [yyyy-mm-dd]

yyyy-mm-dd
2022-04-23

Datetime format in
[hh:mm:ss]

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,24) AS [hh:mm:ss]

hh:mm:ss
15:09:56

Datetime format in

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

Standard: Default

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,100) AS [MMM DD YYYY HH:SS (AM/PM)]

MMM DD YYYY HH:SS (AM/PM)
Apr 23 2022 3:17PM

Datetime format in
[MM/DD/YYYY]
Standard: USA

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,101) AS [MM/DD/YYYY]

MM/DD/YYYY
04/23/2022

Datetime format in
[YYYY.MM.DD]
Standard: ANSI

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,102) AS [YYYY.MM.DD]

YYYY.MM.DD
2022.04.23

Datetime format in
DD/MM/YYYY format
Standard: British/French

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,103) AS [DD/MM/YYYY]

DD/MM/YYYY
23/04/2022

Datetime format in
DD.MM.YY format
Standard: German

DECLARE @DemoDate DATETIME
SET @DemoDate =GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate ,104) AS [DD/MM/YYYY]

DD/MM/YYYY
23.04.2022

Datetime format in
DD-MM-YY format
Standard: Italian

DECLARE @DemoDate DATETIME
SET @DemoDate =GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate ,105) AS [DD/MM/YYYY]

DD/MM/YYYY
23-04-2022

Datetime format in
DD MMM YYYY format
Standard: Shortened month name

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,106) AS [DD MMM YYYY]

DD MMM YYYY
23 Apr 2022

Datetime format in
MMM DD,YYYY format
Standard: Shortened month name

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,107) AS [MMM DD,YYYY]

MMM DD,YYYY
Apr 23, 2022

Datetime Format
In HH:MM: SS
Standard: 24 hour time

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,108) AS [HH:MM:SS]

HH:MM:SS
15:54:05

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

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,109) AS [MMM DD YYYY hh:mm:ss:mmm(AM/PM)]

MMM DD YYYY hh:mm:ss:mmm(AM/PM)
Apr 23 2022 3:57:05:560PM

Datetime format in
MM- DD-YY format
Standard: USA

DECLARE @DemoDate DATETIME
SET @DemoDate =GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate ,110) AS [MM-DD-YYYY]

MM-DD-YYYY
04-23-2022

Datetime format in
YYYY/MM/DD format
Standard: JAPAN

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,111) AS [YYYY/MM/DD]

YYYY/MM/DD
2022/04/23

Datetime format in
YYYYMMDD format
Standard: ISO

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,112) AS [YYYYMMDD]

YYYYMMDD
20220423

Datetime format in
DD MMM YYYY HH:MM:SS: MMM
Standard: Europe default + milliseconds

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,113) AS [DD MMM YYYY HH:MM:SS:MMM]

DD MMM YYYY HH:MM:SS:MMM
23 Apr 2022 16:05:43:323

Datetime format in
HH:MM:SS: MMM
Standard: 24 hour time with milliseconds

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,114) AS [DD MMM YYYY HH:MM:SS:MMM]

DD MMM YYYY HH:MM:SS:MMM
21:45:42:130

Datetime format in
YYYY-MM-DD HH:MM: SS
Default: ODBC canonical

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,120) AS [YYYY-MM-DD HH:MM:SS]

YYYY-MM-DD HH:MM:SS
2022-04-23 21:46:50

Datetime format in
YYYY-MM-DD HH:MM: SS.mmm
Standard: ODBC canonical with milliseconds

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,121) AS [yyyy-mm-ddThh:mi:ss.mmm]

yyyy-mm-ddThh:mi:ss.mmm
2022-04-23 21:49:36.867

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

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,126) AS [mm/dd/yy hh:mm:ss (AM/PM)]

mm/dd/yy hh:mm:ss (AM/PM)
2022-04-23T21:50:01.007

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

DECLARE @DemoDate DATETIME
SET @DemoDate=GETDATE()
SELECT CONVERT(VARCHAR,@DemoDate,130) AS [dd mon yyyy hh:mi:ss:mmm(AM/PM)]

dd mon yyyy hh:mi:ss:mmm(AM/PM)
22 ???

?? 1443 9:50:57:340PM

In the given table, you can see various formats to SQL Server convert date as per our requirements. In the given below table, we 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

DATEADD

You can use the SQL DATEADD function to add a particular period to your date. Suppose you have a requirement to add 1 month to the current date. You can use the SQL DATEADD function to do this task.

The syntax for SQL DATEADD function

DATEADD(interval, number, date)

Interval: You can specify an interval that needs to be added to the specified date. You can have values such as year, quarter, month, day, week, hour, minute, etc.
Number: It specifies the number of intervals to add. For example, if you have a specified interval as Month and Number as 2, it means 2 months need to be added to the date.

.In the given below query, you want to add 2 months to the current date.

SELECT GETDATE() AS TodayDate
SELECT DATEADD(MONTH, 2, GETDATE()) AS TwoMonthAddedDate;

You can see the output as given below.

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

SELECT GETDATE() AS TodayDate
SELECT DATEADD(YEAR, 1, GETDATE()) AS OneYearAddedYearDate;

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

SELECT DATEADD(YEAR,1,GETDATE()) AS TodayDateTime
 ,CONVERT(varchar(110),DATEADD(YEAR,1,GETDATE()),107) AS ConvertedDateTime

Welcome back.

SQL Database

Basic SQL

SQL Date Format and Date Functions in SQL

Data Types for Date and Time in SQL

SQL Convert Date Formats

DATEADD

The syntax for SQL DATEADD function

Related information