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.

Kim
  • 121
  • 3
  • 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 07: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 09: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 - Retired May 11 '18 at 09: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 09:38
  • Could it be leap years? Check your data for values of February 29th. – MJH May 11 '18 at 09:52
  • What does the rest of the query look like? Could you share an estimated execution plan for the query that fails? Perhaps you are filtering out invalid rows in a where clause and in the case it fails the conversion is done earlier in plan than when it works. – Mikael Eriksson May 11 '18 at 11:22
  • Is a screenshot sufficient for the estimated execution plan? – Kim May 11 '18 at 11:40
  • Take the XML and paste it here https://www.brentozar.com/pastetheplan/ and update your question with a link. – Mikael Eriksson May 11 '18 at 11:41
  • I guess you have invalid month names in your second table that gets filtered out by the join to the first table. When the query fails, the conversion is done for all rows in the second table, before the join. – Mikael Eriksson May 11 '18 at 11:52
  • Well, that kind of makes sense. The second table does have names that are not valid month names but wouldn't this be considered a bug then? SELECT comes almost last in the logical processing order and certainly after FROM. See: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-2017#logical-processing-order-of-the-select-statement – Kim May 11 '18 at 12:20
  • 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

0 Answers0