In this programming tutorial we will learn how to format date or date time in sql server. As a web developer i know how important this task is because at any time we may get request from our client to display date in any report of our project according to the format that suits him. We must know different formats of date time. So let's have a look over how to do so.

format date time in sql server using getdate() function

Here's a list of the different date formats that come standard in SQL Server as part of the CONVERT function. It is important to note that the output of these date formats are of VARCHAR data types, so if you have to compare any dates that are in varchar data type then again you have to convert them in datetime data type.

SELECT convert(varchar, getdate(), 100)  -- mon dd yyyy hh:mmAM (or PM)

Output:-
Sep 16 2011 5:55PM

SELECT convert(varchar, getdate(), 101)  --mm/dd/yyyy 

Output:-
09/16/2011

SELECT convert(varchar, getdate(), 102)  --yyyy.mm.dd 

Output:-
2011.09.16

SELECT convert(varchar, getdate(), 105)  --dd-mm-yyyy

Output:-
16-09-2011

SELECT convert(varchar, getdate(), 106)  --dd mon yyyy

Output:-
16 Sep 2011

SELECT convert(varchar, getdate(), 107)  --mon dd, yyyy

Output:-
Sep 16, 2011

SELECT convert(varchar, getdate(), 103)  --dd/mm/yyyy

Output:-
16/09/2011

SELECT convert(varchar, getdate(), 104)  --dd.mm.yyyy

Output:-
16.09.2011

SELECT convert(varchar, getdate(), 108)  --hh:mm:ss

Output:-
17:57:08

SELECT convert(varchar, getdate(), 109)  --mon dd yyyy hh:mm:ss:mmmAM (or PM) 

Output:-
Sep 16 2011 5:57:19:720PM

SELECT convert(varchar, getdate(), 110)  --mm-dd-yyyy

Output:-
09-16-2011

SELECT convert(varchar, getdate(), 111)  --yyyy/mm/dd

Output:-
2011/09/16

SELECT convert(varchar, getdate(), 112)  --yyyymmdd

Output:-
20110916

SELECT convert(varchar, getdate(), 113)  --dd mon yyyy hh:mm:ss:mmm

Output:-
16 Sep 2011 17:59:30:943

SELECT convert(varchar, getdate(), 114)  --hh:mm:ss:mmm(24h)

Output:-
17:59:44:887

SELECT convert(varchar, getdate(), 120)  --yyyy-mm-dd hh:mm:ss(24h)

Output:-
2011-09-16 17:59:55

SELECT convert(varchar, getdate(), 121)  --yyyy-mm-dd hh:mm:ss.mmm

Output:-
2011-09-16 18:00:13.497

SELECT convert(varchar, getdate(), 126)  --yyyy-mm-ddThh:mm:ss.mmm

Output:-
2011-09-16T18:00:28.987

SELECT replace(convert(varchar, getdate(), 111), '/', '')  --yyyy mm dd

Output:-
20110916

SELECT convert(varchar(7), getdate(), 126)   --yyyy-mm

Output:-
2011-09

SELECT right(convert(varchar, getdate(), 106), 8) -- mon yyyy

Output:-
Sep 2011

So this is the way to format date or date time in sql server using getdate() function.

I love your feedback.