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

User · Answer

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

User · Answer

case when isdate inputdate    1  then convert datetime  cast inputdate datetime2   103  else case when isdate inputdate    0  then convert datetime  cast inputdate datetime2   103

User · Answer

This is my favorite use of 112 and 114   select  convert varchar  getdate    112   replace convert varchar  getdate    114           as  Getdate     112   114 or YYYYMMDDHHMMSSMSS    Result      Getdate   112   114 or YYYYMMDDHHMMSSMSS   20171016083349100

User · Answer

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   -- gt  f  vr 8 2013 9 45AM

User · Answer

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

User · Answer

In MS SQL Server you can do   SET DATEFORMAT ymd

[sql] SQL SERVER DATETIME FORMAT

Examples related to sql

Examples related to sql-server

Examples related to datetime