fly36 fly36 - 3 months ago 10
SQL Question

SQL Server : convert string to numeric - Conversion failed when converting date and/or time from character string

I got a dataset in SQL Server Management Studio.

The data looks like the following

month Year
-------------
Feb 2016
Jan 2015


I want to create a numeric date using 01 as dd. I tried

CAST('01-'+ month +'-'+ year AS DATE)


also

CONVERT(DATETIME, '01-'+ month +'-'+ year, 106) AS


Both of them cause this error:


Conversion failed when converting date and/or time from character string.


Can anyone teach me how to covert the string into numeric datetime/date please.

Answer

Perhaps the reason is the hyphens. You should try with spaces instead:

convert(datetime, '01 '+ month +' '+ year, 106)

Another possibility is that month contains invalid values. In SQL Server 2012+, use try_convert() to avoid that problem:

try_convert(datetime, '01 '+ month +' '+ year, 106)
Comments