SQL SERVER DATETIME FORMAT

Question

Studying SQL Server there is something I am not sure of:

A datetime field with the value: 2012-02-26 09:34:00.000

If I select out of the table using:

CAST(dob2 AS VARCHAR(12) ) AS d1

It formats it as: Feb 26 2012

What I am unsure of his how or why SQL Server formats DateTime like that. If you use datetime2 it does not - anyone know why?

Answer 1

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

Answer 2

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