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

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

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

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

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

In MS SQL Server you can do   SET DATEFORMAT ymd

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

[sql] SQL SERVER DATETIME FORMAT

Examples related to sql

Examples related to sql-server

Examples related to datetime