Format DateTime in SQL

In SQL Server, when we want to format DATETIME value, we can use CAST or CONVERT function to convert DATETIME to VARCHAR with a specific format.
The different between two function is:
When you use CAST, it change the DATETIME value to the type of data that you specified
For example:

SELECT CAST(GETDATE() AS VARCHAR(23))
--RETURNS 'Jan  1 2011 10:48PM'

But when you use CONVERT function, you can change the date format

SELECT CONVERT(VARCHAR(23), GETDATE(), 103)
--RETURNS 01/01/2011

103 here is the default format style

Run the below code in SQL and you will find out all the default format available

DECLARE @i INT,
 @dt DATETIME
SET @i = 0
SET @dt = '2010-12-31 14:05:06.123'
WHILE @i < 150
BEGIN
 BEGIN TRY
  PRINT (CONVERT(VARCHAR, @i) + ' : ' + CONVERT (VARCHAR, @dt, @i))
  SET @i = @i + 1
 END TRY
 BEGIN CATCH
  SET @i = @i + 1
 END CATCH
END

You can change the value of @dt or @i to match your condition, maybe there will be more formats in the future, so you must change @i to 1000 to display them all for example xDxDxD.

Here is all the default formats available. I use '2010-12-31 14:05:06.123' for the date

Format Style	Result
0	Dec 31 2010 2:05PM
1	12/31/10
2	10.12.31
3	31/12/10
4	31.12.10
5	31-12-10
6	31 Dec 10
7	Dec 31, 10
8	14:05:06
9	Dec 31 2010 2:05:06:123PM
10	12-31-10
11	10/12/31
12	101231
13	31 Dec 2010 14:05:06:123
14	14:05:06:123
20	2010-12-31 14:05:06
21	2010-12-31 14:05:06.123
22	12/31/10 2:05:06 PM
23	2010-12-31
24	14:05:06
25	2010-12-31 14:05:06.123
100	Dec 31 2010 2:05PM
101	12/31/2010
102	2010.12.31
103	31/12/2010
104	31.12.2010
105	31-12-2010
106	31 Dec 2010
107	Dec 31, 2010
108	14:05:06
109	Dec 31 2010 2:05:06:123PM
110	12-31-2010
111	2010/12/31
112	20101231
113	31 Dec 2010 14:05:06:123
114	14:05:06:123
120	2010-12-31 14:05:06
121	2010-12-31 14:05:06.123
126	2010-12-31T14:05:06.123
127	2010-12-31T14:05:06.123
130	25 ???? 1432 2:05:06:123PM
131	25/01/1432 2:05:06:123PM

Hope this entry help you!
If you found any mistake or error in this entry, please let me know. I'll try to fix that a.s.a.p.
Any solution is highly appreciated!
-Share2Learn-