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
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.
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.
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.