0

How can one convert the string "01 December 2016" to a date type in SQL Server?

CONVERT(date, '01 December 2016', 106)

Expected date outcome "01 Dec 2016"

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
danny
  • 1,969
  • 3
  • 16
  • 26
  • 1
    Your code works. SQL Server (correctly) returns 2016-12-01, because that is the format for returning dates. If you want a different format, then use `convert(varchar())` with the appropriate format argument. Or `format()`. – Gordon Linoff Apr 04 '17 at 14:09

2 Answers2

3

If 2012+ you can use Format()

Select Format(convert(date,'01 December 2016'),'dd MMM yyyy')

Returns

01 Dec 2016
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • Calling `CONVERT` like above means to rely on implicit culture/language conversions. On my system this would break. +1 for the `FORMAT()` – Shnugo Apr 04 '17 at 14:38
1

It is very dangerous to work with culture specific date/time formats and it is even worse to work with language and culture specific formats...

If ever possible store date/time values in appropriate types!

In my (german) system a direct cast or convert would break due to "December", which is "Dezember" in Germany. Have a look at this:

SET LANGUAGE English; --try the same with "German"

DECLARE @d VARCHAR(100)='01 December 2016';

SELECT CONVERT(VARCHAR(11),CONVERT(DATE,@d,106),106);

The result

01 Dec 2016

One should never rely on implicit conversions: Call CONVERT with the third parameter in any case!

The third parameter 106 tells SQL Server the date's format (how to parse it). The first CONVERTSs target type is DATE. This - now properly represented! - date can be converted again to VARCHAR(11) with 106 as third parameter now specifying the output format.

For deeper insight in language specific date parts you can run this query:

SELECT * FROM sys.syslanguages;

btw: If you are using SQL Server 2012+ you should call FORMAT() as pointed out by John Cappelletti

Shnugo
  • 66,100
  • 9
  • 53
  • 114