1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
fly36
  • 513
  • 2
  • 6
  • 23
  • Possible duplicate of [Sql Server string to date conversion](http://stackoverflow.com/questions/207190/sql-server-string-to-date-conversion) – fedorqui Aug 19 '16 at 13:43
  • 2
    Now that you have a solution you should consider changing your datatype to store date information in the proper datatypes. In this case probably date. Storing this as strings is full of challenges that are just not needed. – Sean Lange Aug 19 '16 at 14:05

3 Answers3

1

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)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

Another option is to use concat with convert if 2012+

Select convert(datetime, concat(1,month ,year), 106)
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • It should be noted that `CONCAT` is new in SQL Server **2012**, so if the OP uses an earlier version, he won't be able to use this – marc_s Aug 19 '16 at 14:16
0

Use this code 

convert(datetime, concat('01-', mnth , '-',yr), 106) AS d
Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38
  • It should be noted that `CONCAT` is new in SQL Server **2012**, so if the OP uses an earlier version, he won't be able to use this – marc_s Aug 19 '16 at 14:16