By default, result for select getdate() in MS-SQL will be something like below:

getdate()
————————-

2008-09-09 15:01:47.340

CONVERT function can be use to manipulate the date format as per our need.

Using CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

example:

select CONVERT(varchar(25),getdate(),100)

————————-
Sep 9 2008 3:04PM

Below are the lists of style/format for reference :

 

0 or 100	mon dd yyyy hh:miAM (or PM)
101	mm/dd/yy
102	yy.mm.dd
103	dd/mm/yy
104	dd.mm.yy
105	dd-mm-yy
106	dd mon yy
107	Mon dd, yy
108	hh:mm:ss
9 or 109	mon dd yyyy hh:mi:ss:mmmAM (or PM)
110	mm-dd-yy
111	yy/mm/dd
112	yymmdd
13 or 113	dd mon yyyy hh:mm:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
20 or 120	yyyy-mm-dd hh:mi:ss(24h)
21 or 121	yyyy-mm-dd hh:mi:ss.mmm(24h)
126	yyyy-mm-dd Thh:mm:ss:mmm(no spaces)
130	dd mon yyyy hh:mi:ss:mmmAM
131	dd/mm/yy hh:mi:ss:mmmAM

Once I have a problem to display a date format like ddmmyy whereas it’s not in the table list. Well, solution is to use below query. Do let me know if you have a better solution.

 

select replace(CONVERT(varchar(10),getdate(),104),'.','')

————
09092008

 

That’s all for today. Happy Fasting !