Date and Time Conversions Using SQL Server

Date and Time Conversions Using SQL Server

In this article, how will convert date and time in SQL, first considerations is the actual date/time needed. The most common is the current date/time using getdate(). This provides the current date and time according to the server providing the date and time. If a universal date/time is needed, then getutcdate() should be used.

 

How to get different SQL Server date formats

  1. Use the date format option along with CONVERT function
  2. To get MM/DD/YYYY use SELECT CONVERT(varchar, getdate(), 1)
  3. To get YYYY-MM-DD use SELECT CONVERT(varchar, getdate(), 23)
  4. Check out the chart to get a list of all format options

 

Example '2020-12-01 00:10:50.841'

 


DECLARE @counter INT = 0
DECLARE @date DATETIME = '2020-12-01 00:10:50.841'

CREATE TABLE #dateFormat (DateFormatOption int, DateOutput nvarchar(40))

WHILE (@counter <= 145 )
BEGIN
   BEGIN TRY
      INSERT INTO #dateFormat
      SELECT CONVERT(nvarchar, @counter), CONVERT(nvarchar,@date, @counter) 
      SET @counter = @counter + 1
   END TRY
   BEGIN CATCH;
      SET @counter = @counter + 1
      IF @counter >= 145
      BEGIN
         BREAK
      END
   END CATCH
END

SELECT * FROM #dateFormat
DROP TABLE #dateFormat

 

Output

 

DateFormatOption DateOutput
0 Dec  1 2020 12:10AM
1 12/01/2020
2 20.12.01
3 01/12/2020
4 01.12.20
5 01/12/2020
6 01-Dec-20
7 Dec 01, 20
8 00:10:50
9 Dec  1 2020 12:10:50:840AM
10 12/01/2020
11 20/12/01
12 201201
13 01 Dec 2020 00:10:50:840
14 00:10:50:840
20 12/01/2020 00:10
21 10:50.8
22 12/01/2020 00:10
23 12/01/2020
24 00:10:50
25 10:50.8
100 Dec  1 2020 12:10AM
101 12/01/2020
102 2020.12.01
103 01/12/2020
104 01.12.2020
105 01/12/2020
106 01-Dec-20
107 Dec 01, 2020
108 00:10:50
109 Dec  1 2020 12:10:50:840AM
110 12/01/2020
111 12/01/2020
112 20201201
113 01 Dec 2020 00:10:50:840
114 00:10:50:840
120 12/01/2020 00:10
121 10:50.8
126 2020-12-01T00:10:50.840
127 2020-12-01T00:10:50.840
130 16 ربيع الثاني 1442 12:10:50:8
131 16/04/1442 12:10:50:840AM

 

DATE ONLY FORMATS
Format # Query Output
1 select convert(varchar, getdate(), 1) 12/01/20
2 select convert(varchar, getdate(), 2) 20.12.01
3 select convert(varchar, getdate(), 3) 01/12/20
4 select convert(varchar, getdate(), 4) 01.12.20
5 select convert(varchar, getdate(), 5) 01-12-20
6 select convert(varchar, getdate(), 6) 01 Dec 20
7 select convert(varchar, getdate(), 7) Dec 01, 20
10 select convert(varchar, getdate(), 10) 12-01-20
11 select convert(varchar, getdate(), 11) 20/12/01
12 select convert(varchar, getdate(), 12) 201201
23 select convert(varchar, getdate(), 23) 2020-12-01
101 select convert(varchar, getdate(), 101) 12/01/2020
102 select convert(varchar, getdate(), 102) 2020.12.01
103 select convert(varchar, getdate(), 103) 01/12/2020
104 select convert(varchar, getdate(), 104) 01.12.2020
105 select convert(varchar, getdate(), 105) 01-12-2020
106 select convert(varchar, getdate(), 106) 01 Dec 2020
107 select convert(varchar, getdate(), 107) Dec 01, 2020
110 select convert(varchar, getdate(), 110) 12-01-2020
111 select convert(varchar, getdate(), 111) 2020/12/01
112 select convert(varchar, getdate(), 112) 20201201
     
TIME ONLY FORMATS
8 select convert(varchar, getdate(), 8) 00:10:50
14 select convert(varchar, getdate(), 14) 00:10:50:841
24 select convert(varchar, getdate(), 24) 00:10:50
108 select convert(varchar, getdate(), 108) 00:10:50
114 select convert(varchar, getdate(), 114) 00:10:50:841
     
DATE & TIME FORMATS
0 select convert(varchar, getdate(), 0) Dec 12 2020 12:10AM
9 select convert(varchar, getdate(), 9) Dec 01 2020 12:10:50:841AM
13 select convert(varchar, getdate(), 13) 01 Dec 2020 00:10:50:841AM
20 select convert(varchar, getdate(), 20) 2020-12-01 00:10:50
21 select convert(varchar, getdate(), 21) 2020-12-01 00:10:50.841
22 select convert(varchar, getdate(), 22) 12/01/20 12:10:50 AM
25 select convert(varchar, getdate(), 25) 2020-12-01 00:10:50.841
100 select convert(varchar, getdate(), 100) Dec 01 2020 12:10AM
109 select convert(varchar, getdate(), 109) Dec 01 2020 12:10:50:841AM
113 select convert(varchar, getdate(), 113) 01 Dec 2020 00:10:50:841
120 select convert(varchar, getdate(), 120) 2020-12-01 00:10:50
121 select convert(varchar, getdate(), 121) 2020-12-01 00:10:50.841
126 select convert(varchar, getdate(), 126) 2020-12-01T00:10:50.841
127 select convert(varchar, getdate(), 127) 2006-12-30T00:38:54.840
     
ISLAMIC CALENDAR DATES
130 select convert(nvarchar, getdate(), 130) 16 ربيع الثاني 1442 12:10:50:8
131 select convert(nvarchar, getdate(), 131) 16/04/1442 12:10:50:840AM

 

 

 

2021-10-03