15

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ian
  • 900
  • 2
  • 9
  • 19
  • 1
    You can use **[`CONVERT()`](http://msdn.microsoft.com/en-us/library/ms187928.aspx)** to choose how a date(time) is formatted. – ypercubeᵀᴹ Feb 08 '13 at 09:39
  • 5
    Look at the remarks to Date and Time Styles here: [CAST and CONVERT](http://msdn.microsoft.com/en-us/library/ms187928.aspx) – Tim Schmelter Feb 08 '13 at 09:50

7 Answers7

7

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

SQLGuru
  • 1,099
  • 5
  • 14
7

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
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • The date format is not only dependent on the language setting, it can be overridden by `SET DATEFORMAT`. See the "Remarks" section here http://technet.microsoft.com/en-us/library/ms189491.aspx – Tony Sep 15 '13 at 23:00
5

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

Kaf
  • 33,101
  • 7
  • 58
  • 78
1

In MS SQL Server you can do:

SET DATEFORMAT ymd
bluish
  • 26,356
  • 27
  • 122
  • 180
ch2o
  • 815
  • 2
  • 12
  • 29
0
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)
Jeff
  • 12,555
  • 5
  • 33
  • 60
jai
  • 11
0

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

THE LIFE-TIME LEARNER
  • 1,476
  • 1
  • 8
  • 18
Hank Freeman
  • 1,192
  • 8
  • 7
0

to change the date format by using sql syntax you should use this query

SELECT DATE_FORMAT(`<columnName>`, '%d/%m/%Y') FROM schemaname.tablename;

ex:-

for suppose i have a schema named as bugloo and the table name is tbl_company and in this tbl_company i have a column all are in the date format %yy/%mm/%dd and column name is createdDate and the query should like this

SELECT DATE_FORMAT(`createdDate`, '%d/%m/%Y') FROM bugloo.tbl_company;

after running this query my output date would be converted to %dd/%mm/%yyyy

Syscall
  • 19,327
  • 10
  • 37
  • 52
Mahesh P
  • 1
  • 1