2

Which user/db setting would cause the date conversion here to fail with Conversion failed when converting date and/or time from character string?

SELECT
    -- This works:
    CONVERT(datetime2, '01 ' + LEFT([MonthName], 3) + ' ' + CAST([Year] as char(4)), 106)

    -- This doesn't:
    ,CONVERT(datetime2, '01 ' + LEFT([MonthName], 3) + ' 2018', 106)

[Year] is of type int NOT NULL.

[MonthName] is of type nvarchar(100) NOT NULL and its collation is SQL_Latin1_General_CP1_CI_AS.

DBCC USEROPTIONS shows this:

language   | us_english
dateformat | mdy

I tried creating another table with seemingly the same settings and the query works there. Something is obviously different but I can't figure out what.

EDIT

Doing a SELECT DISTINCT [Year], [MonthName] reveals that there's data only for 'December', 2017. So yes, the month name is in English.

Leap years can't be the problem because of the previous point but also because I'm forcing the day to be 1.

Interestingly, TRY_CONVERT doesn't return NULL for any row. I did a

WHERE TRY_CONVERT(datetime2, '01 ' + LEFT([MonthName], 3) + ' 2018', 106) IS NULL

I'm suspecting that inputting the ' 2018' causes the resulting string to be of some other collation (or something) which makes SQL Server expect the date format to be something different.

Here's the weird part. The query works if it's like this:

SELECT DISTINCT
    CONVERT(datetime2, + LEFT([MonthName], 3) + '01 2018', 106)
    ,CONVERT(datetime2, '01 ' + LEFT([MonthName], 3) + ' 2018', 106)
    ,[Year]

But removing either the [Year] or the DISTINCT will again produce the same error.

The query has only two tables joined in the FROM clause and no WHERE clause. The [MonthName] comes from the second table and the [Year] from the first.

Improve this question
  • If you set off individual select... statements for each CONVERT() what error message do you receive for the second statement? – John K. N. May 11 '18 at 7:11
  • If the second CONVERT() is the only statement in the SELECT statement, I get the Conversion failed when converting date and/or time from character string error. – Kim May 11 '18 at 9:27
  • Using TRY_CONVERT instead of CONVERT and including the base columns of your select might reveal something with your data. TRY_CONVERT will return NULL if the conversion fails. – Scott Hodgin May 11 '18 at 9:33
  • What data is stored in the MonthName column? Are you using English month names or possibly another language? – John K. N. May 11 '18 at 9:38
  • 1
    That is the logical order of processing. What you have to deal with when writing the query. After the query optimizer is done with creating an execution plan it can look very different. From the link you provided "The actual physical execution of the statement is determined by the query processor and the order may vary from this list." – Mikael Eriksson May 11 '18 at 12:39