Convert DateTime To Different Formats In SQL Server

In this article, we will learn how to convert DateTime to different formats in SQL Server.

Here we will use the “CONVERT” function to convert a datetime into different format in SQL Server.

By using some built-in function in SQL Server we can get the datetime value in specific format.

For example,

  • GETDATE(): It returns server datetime in “YYYY-MM-DD HH:mm:ss.fff” format.
  • GETUTCDATE(): It returns datetime in GMT.
  • SYSDATETIME(): It returns server’s datetime
  • SYSDATETIMEOFFSET(): It returns server’s datetime with time zone on which SQL Server instance is running.
  • SYSUTCDATETIME(): It returns server datetime in GMT.
  • CURRENT_TIMESTAMP: It returns current datetime of the server.
SELECT GETDATE() AS [GETDATE()], 
       GETUTCDATE() AS [GETUTCDATE()],
       SYSDATETIME() AS [SYSDATETIME()],
       SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET()],
       SYSUTCDATETIME() AS [SYSUTCDATETIME()],
       CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP]; 
Result: GETDATE():       		2022-01-02 09:49:53.077
	    GETUTCDATE():		    2022-01-02 04:19:53.077
        SYSDATETIME():		    2022-01-02 09:49:53.0783654
        SYSDATETIMEOFFSET():	2022-01-02 09:49:53.0783654 +05:30
        SYSUTCDATETIME():		2022-01-02 04:19:53.0783654
        CURRENT_TIMESTAMP: 		2022-01-02 09:49:53.077

By using the “CONVERT()” function we can convert the date and time to a different format.

Syntax

CONVERT(Datatype, Date, Formatcode)

It accepts three arguments.

  • Datatype: It defines the target data type we want to be converted
  • Date: It defines the date value we want to convert.
  • Fromatcode: It defines the output format of the date value.
SELECT CONVERT(VARCHAR, GETDATE(), 1)
Result: 01/02/22

There are various datetime format codes, by using this code we can change the format of a datetime.

MM/DD/YY

By using format code as 1 we can get datetime in “MM/DD/YY” format.

SELECT CONVERT(VARCHAR, GETDATE(), 1)
Result: 01/02/22

YY.MM.DD

By using format code as 2 we can get datetime in “YY.MM.DD” format.

SELECT CONVERT(VARCHAR, GETDATE(), 2)
Result: 22.01.02

DD/MM/YY

By using format code as 3 we can get datetime in “DD/MM/YY” format.

SELECT CONVERT(VARCHAR, GETDATE(), 3)
Result: 02/01/22

DD.MM.YY

By using format code as 4 we can get datetime in “DD.MM.YY” format.

SELECT CONVERT(VARCHAR, GETDATE(), 4)
Result: 02.01.22

DD-MM-YY

By using format code as 5 we can get datetime in “DD-MM-YY” format.

SELECT CONVERT(VARCHAR, GETDATE(), 5)
Result: 02-01-22

DD MMM YY

By using format code as 6 we can get datetime in “DD MMM YY” format.

SELECT CONVERT(VARCHAR, GETDATE(), 6)
Result: 02 Jan 22

MMM DD, YY

By using format code as 7 we can get datetime in “MMM DD, YY” format.

SELECT CONVERT(VARCHAR, GETDATE(), 7)
Result: Jan 02, 22

MMM DD YYYY hh:mm:ss:fff(AM/PM)

By using format code as 9 we can get datetime in “MMM DD YYYY hh:mm:ss:fff(AM/PM)” format.

SELECT CONVERT(VARCHAR, GETDATE(), 9)
Result: Jan  4 2022 10:28:42:097AM

MM-DD-YY

By using format code as 10 we can get datetime in “MM-DD-YY” format.

SELECT CONVERT(VARCHAR, GETDATE(), 10)
Result: 01-02-22

YY/MM/DD

By using format code as 11 we can get datetime in “YY/MM/DD” format.

SELECT CONVERT(VARCHAR, GETDATE(), 11)
Result: 22/02/01

YYMMDD

By using format code as 12 we can get datetime in “YYMMDD” format.

SELECT CONVERT(VARCHAR, GETDATE(), 12)
Result: 220401

DD MMM YYYY HH:mm:ss:fff

By using format code as 13 we can get datetime in “DD MMM YYYY HH:mm:ss:fff” format.

SELECT CONVERT(VARCHAR, GETDATE(), 13)
Result: 02 Jan 2022 10:43:52:753

HH:mm:ss:fff

By using format code as 14 we can get datetime in “HH:mm:ss:fff” format.

SELECT CONVERT(VARCHAR, GETDATE(), 14)
Result: 10:45:16:290

YYYY-MM-DD HH:mm:ss

By using format code as 20 we can get datetime in “YYYY-MM-DD HH:mm:ss” format.

SELECT CONVERT(VARCHAR, GETDATE(), 20)
Result: 2022-01-02 10:46:58

YYYY-MM-DD HH:mm:ss.fff

By using format code as 21 we can get datetime in “YYYY-MM-DD HH:mm:ss.fff” format.

SELECT CONVERT(VARCHAR, GETDATE(), 21)
Result: 2022-01-02 10:47:58.393

MM/DD/YY hh:mm:ss (AM/PM)

By using format code as 22 we can get datetime in “MM/DD/YY hh:mm:ss (AM/PM)” format.

SELECT CONVERT(VARCHAR, GETDATE(), 22)
Result: 01/02/22 10:49:36 AM

YYYY-MM-DD

By using format code as 23 we can get datetime in “YYYY-MM-DD” format.

SELECT CONVERT(VARCHAR, GETDATE(), 23)
Result: 2022-01-02

HH:mm:ss

By using format code as 24 we can get datetime in “HH:mm:ss” format.

SELECT CONVERT(VARCHAR, GETDATE(), 24)
Result: 10:51:38

YYYY-MM-DD HH:mm:ss.fff

By using format code as 25 we can get datetime in “YYYY-MM-DD HH:mm:ss.fff” format.

SELECT CONVERT(VARCHAR, GETDATE(), 25)
Result: 2022-01-02 10:55:30.010

MMM DD YYYY hh: ss (AM/PM)

By using format code as 100 we can get datetime in “MMM DD YYYY hh: ss (AM/PM)” format.

SELECT CONVERT(VARCHAR, GETDATE(), 100)
Result: Jan  4 2022 10:58AM

MM/DD/YYYY

By using format code as 101 we can get datetime in “MM/DD/YYYY” format.

SELECT CONVERT(VARCHAR, GETDATE(), 101)
Result: 01/02/2022

YYYY.MM.DD

By using format code as 102 we can get datetime in “YYYY.MM.DD” format.

SELECT CONVERT(VARCHAR, GETDATE(), 102)
Result: 2022.01.02

DD/MM/YYYY

By using format code as 103 we can get datetime in “DD/MM/YYYY” format.

SELECT CONVERT(VARCHAR, GETDATE(), 103)
Result: 02/01/2022

DD.MM.YY

By using format code as 104 we can get datetime in “DD.MM.YY” format.

SELECT CONVERT(VARCHAR, GETDATE(), 104)
Result: 02.01.2022

DD-MM-YY

By using format code as 105 we can get datetime in “DD-MM-YY” format.

SELECT CONVERT(VARCHAR, GETDATE(), 105)
Result: 02-01-2022

DD MMM YYYY

By using format code as 106 we can get datetime in “DD MMM YYYY” format.

SELECT CONVERT(VARCHAR, GETDATE(), 106)
Result: 02 Jan 2022

MMM DD, YYYY

By using format code as 107 we can get datetime in “MMM DD, YYYY” format.

SELECT CONVERT(VARCHAR, GETDATE(), 107)
Result: Jan 02, 2022

HH:mm: ss

By using format code as 108 we can get datetime in “HH:mm: ss” format.

SELECT CONVERT(VARCHAR, GETDATE(), 108)
Result: 11:06:17

MMM DD YYYY hh:mm:ss:fff(AM/PM)

By using format code as 109 we can get datetime in “MMM DD YYYY hh:mm:ss:fff(AM/PM)” format.

SELECT CONVERT(VARCHAR, GETDATE(), 109)
Result: Jan  4 2022 11:07:17:280AM

MM- DD-YY

By using format code as 110 we can get datetime in “MM- DD-YY” format.

SELECT CONVERT(VARCHAR, GETDATE(), 110)
Result: 01-02-2022

YYYY/MM/DD

By using format code as 111 we can get datetime in “YYYY/MM/DD” format.

SELECT CONVERT(VARCHAR, GETDATE(), 111)
Result: 2022/01/02

YYYYMMDD

By using format code as 112 we can get datetime in “YYYYMMDD” format.

SELECT CONVERT(VARCHAR, GETDATE(), 112)
Result: 20220104

DD MMM YYYY HH:mm:ss: fff

By using format code as 113 we can get datetime in “DD MMM YYYY HH:mm:ss: fff” format.

SELECT CONVERT(VARCHAR, GETDATE(), 113)
Result: 02 Jan 2022 11:11:30:503

HH:mm:ss: fff

By using format code as 114 we can get datetime in “HH:mm:ss: fff” format.

SELECT CONVERT(VARCHAR, GETDATE(), 114)
Result: 11:12:13:973

YYYY-MM-DD HH:mm: ss

By using format code as 120 we can get datetime in “YYYY-MM-DD HH:mm: ss” format.

SELECT CONVERT(VARCHAR, GETDATE(), 120)
Result: 2022-01-02 11:13:13

YYYY-MM-DD HH:mm: ss.fff

By using format code as 121 we can get datetime in “YYYY-MM-DD HH:mm: ss.fff” format.

SELECT CONVERT(VARCHAR, GETDATE(), 121)
Result: 2022-01-02 11:14:10.470

YYYY-MM-DDTHH:mm: ss.fff

By using format code as 126/127 we can get datetime in “YYYY-MM-DDTHH:mm: ss.fff” format.

SELECT CONVERT(VARCHAR, GETDATE(), 126)
SELECT CONVERT(VARCHAR, GETDATE(), 127)
Result: 2022-01-04T11:15:03.223

YYYY-MM-DDTHH:mm: ss.fff

By using format code as 130/131 we can get datetime in “Islamic/Hijri” date format.

SELECT CONVERT(VARCHAR, GETDATE(), 131)
Result: 1/06/1443 11:24:36:480AM

In the above article, we learned how to convert DateTime to different formats in SQL Server.

Hope this will help the readers. Happy Coding!!!