|
SQL Server Date Time Format
In SQL Server used Cast or Convert function to Format DateTime value or column into a specific date format.Both function are used to convert datetime to varchar or string.
CAST function Syntax: CAST(expression as data_type)
Let's convert current date time to varchar
select cast(getdate() as varchar)
CONVERT function is used to change or convert the DateTime formats.By using convert function you can get only Date part or only Time part from the datetime.
CONVERT Function Syntax: CONVERT(data_type,expression,date Format style)
Let's take Sql Server DateTtime styles example:
|
Format |
Query |
|
USA mm/dd/yy |
select convert(varchar, getdate(), 1) |
|
ANSI yy.mm.dd |
select convert(varchar, getdate(), 2) |
|
British/French dd/mm/yy |
select convert(varchar, getdate(), 3) |
|
German dd.mm.yy |
select convert(varchar, getdate(), 4) |
|
Italian dd-mm-yy |
select convert(varchar, getdate(), 5) |
|
dd mon yy |
select convert(varchar, getdate(), 6) |
|
Mon dd, yy |
select convert(varchar, getdate(), 7) |
|
USA mm-dd-yy |
select convert(varchar, getdate(), 10) |
|
JAPAN yy/mm/dd |
select convert(varchar, getdate(), 11) |
|
ISO yymmdd |
select convert(varchar, getdate(), 12) |
|
mon dd yyyy hh:miAM (or PM) |
select convert(varchar, getdate(), 100) |
|
mm/dd/yyyy |
select convert(varchar, getdate(), 101) |
|
yyyy.mm.dd |
select convert(varchar, getdate(), 102) |
|
dd/mm/yyyy |
select convert(varchar, getdate(), 103) |
|
dd.mm.yyyy |
select convert(varchar, getdate(), 104) |
|
dd-mm-yyyy |
select convert(varchar, getdate(), 105) |
|
dd mon yyyy |
select convert(varchar, getdate(), 106) |
|
Mon dd, yyyy |
select convert(varchar, getdate(), 107) |
|
hh:mm:ss |
select convert(varchar, getdate(), 108) |
|
Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM) |
select convert(varchar, getdate(), 109) |
|
mm-dd-yyyy |
select convert(varchar, getdate(), 110) |
|
yyyy/mm/dd |
select convert(varchar, getdate(), 111) |
|
yyyymmdd |
select convert(varchar, getdate(), 112) |
|
Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h) |
select convert(varchar, getdate(), 113) or select convert(varchar, getdate(), 13) |
|
hh:mi:ss:mmm(24h) |
select convert(varchar, getdate(), 114) |
Related Articles:
|
User Comments:
Comment By
Harry
on
09/24/2009
How do I convert datetime to the format HH.MM.SS as
opposed to HH:MM:SS
Comment By
dev1
on
02/17/2010
Hi Can you please tell me how can i find only privious date to current date in only dd/mm/yy and no time with this
Comment By
brian krause
on
08/13/2010
im confused on how these work within a sql query.
i would wnat to format the field shipdate so that it looks like 9:00:00 right now in my table it looks like 90000
Thanks.
Comment By
Isaac Chongthu
on
09/26/2010
try this:
http://www.sql-server-helper.com/tips/date-formats.aspx
Comment By
Bhavesh
on
01/06/2011
I want to convert 'dd/MM/yyyy' to 'yyyy-MM-dd'.
Comment By
fatma
on
01/08/2011
where wright this select convert(varchar, getdate(), 103)
for have dd/mm/yyyy
idont kon woh to use
Comment By
Maha
on
01/30/2011
m confused on how these work within a sql query. i would wnat to format the field shipdate so that it looks like 9:00:00 right now in my table it looks like 90000 Thanks.
Comment By
Maha
on
01/30/2011
Very useful though want more format
Comment By
sravan
on
01/31/2011
how can i convert date format in sql server mm/dd/yyyy to
dd/mm/yyyy...
as sql default takes date as mm/dd/yyyy...
wat is the actual code for it.
Comment By
rakesh sharma
on
03/22/2011
hey can u plz help me on dis....
what is dis date format
"2009-09-07 12:53:46.807000000"
plz its urgent
Comment By
mokie
on
03/31/2011
how can i convert dd/mm/yyyy to yyyy/mm/dd format?
i need to accept dd/mm/yyyy as variable from excel macro and send it to retrieve data in ms sql using format yyyy/mm/dd?? anyone can help?? tq...
Comment By
Ram Samuj
on
04/07/2011
i want to Date Time Format like this
25/04/2011 03:25 PM
Comment By
ranjeet
on
04/20/2011
i want code in sql
i want inter date in my table
give total code
Post Your Comment :
|