Date Formats in SQL Server

In this post, you’ll learn how to use Convert function in SQL queries to display various SQL Server Date Time formats.

There are many times when the date and time doesn’t show like that way you wanted to display in your application. You will need to format the data in the application or use the built-in SQL Server functions to format the date time like you want.

Date Formats in SQL Server

To get the various date time formats, we can perform the following

  • Use the CONVERT function and the date format option.
  • Eg : To get the date format in YYYY-MM-DD , you will need to use the code like this : SELECT CONVERT(varchar(10), getdate(), 23) where parameter 1 is the data type that is returned , 2nd parameter the date that you want to be formatted and the third parameter is the date format option.

Below is collection of all possible Standard Date Time Formats in SQL Server.

Date FormatSQL StatementSample Output
MM/DD/YYSELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]02/22/21
MM/DD/YYYYSELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]02/22/2021
YY.MM.DDSELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]21.02.22
YYYY.MM.DDSELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]2021.02.22
DD/MM/YYSELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]22/02/21
DD/MM/YYYYSELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]22/02/2021
DD.MM.YYSELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]22.02.21
DD.MM.YYYYSELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]22.02.2021
DD-MM-YYSELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]22-02-21
DD-MM-YYYYSELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]22-02-2021
DD Mon YY SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]22 Feb 21
DD Mon YYYYSELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]22 Feb 2021
Mon DD, YY SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]Feb 22, 21
Mon DD, YYYYSELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]Feb 22, 2021
MM-DD-YYYYSELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]02-22-2021
YY/MM/DDSELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]21/02/22
YYYY/MM/DDSELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]2021/02/22
YYMMDDSELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]210222
YYYYMMDDSELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]20210222
Mon DD YYYY
HH:MIAM (or PM)
SELECT CONVERT(VARCHAR(20), GETDATE(), 100)Feb 22 2021 10:27PM
DD Mon YYYY HH:MM:SS:MMM(24h) SELECT CONVERT(VARCHAR(24), GETDATE(), 113)22 Feb 2021 22:27:22:440
HH:MI:SS:MMM(24H)SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]22:27:36:007
YYYY-MM-DD HH:MI:SS(24h)SELECT CONVERT(VARCHAR(19), GETDATE(), 120)2021-02-22 22:27:49
YYYY-MM-DD HH:MI:SS.MMM(24h)SELECT CONVERT(VARCHAR(23), GETDATE(), 121)2021-02-22 22:27:58.730
YYYY-MM-DDTHH:MM:SS:MMMSELECT CONVERT(VARCHAR(23), GETDATE(), 126)2021-02-22T22:28:07.613
close

Copy and paste this code to display the image on your site

%d bloggers like this: