find and solve || findandsolve.com
Please wait.....

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