SQL SERVER DATETIME FORMAT
try this:
select convert(varchar, dob2, 101)
select convert(varchar, dob2, 102)
select convert(varchar, dob2, 103)
select convert(varchar, dob2, 104)
select convert(varchar, dob2, 105)
select convert(varchar, dob2, 106)
select convert(varchar, dob2, 107)
select convert(varchar, dob2, 108)
select convert(varchar, dob2, 109)
select convert(varchar, dob2, 110)
select convert(varchar, dob2, 111)
select convert(varchar, dob2, 112)
select convert(varchar, dob2, 113)
refernces: http://msdn.microsoft.com/en-us/library/ms187928.aspx
http://www.w3schools.com/sql/func_convert.asp
Compatibility Supports Says that
Under compatibility level 110, the default style for CAST and CONVERT
operations on time
and datetime2
data types is always 121. If your query relies on the old behavior, use a compatibility level less than 110, or explicitly specify the 0 style in the affected query.
That means by default datetime2
is CAST as varchar
to 121 format
. For ex; col1
and col2
formats (below) are same (other than the 0s at the end)
SELECT CONVERT(varchar, GETDATE(), 121) col1,
CAST(convert(datetime2,GETDATE()) as varchar) col2,
CAST(GETDATE() as varchar) col3
SQL FIDDLE DEMO
--Results
COL1 | COL2 | COL3
2013-02-08 09:53:56.223 | 2013-02-08 09:53:56.2230000 | Feb 8 2013 9:53AM
FYI, if you use CONVERT
instead of CAST
you can use a third parameter to specify certain formats as listed here on MSDN
The default date format depends on the language setting for the database server. You can also change it per session, like:
set language french
select cast(getdate() as varchar(50))
-->
févr 8 2013 9:45AM
In MS SQL Server you can do:
SET DATEFORMAT ymd