format date time in sql server using getdate() function

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.

0 comments: