MSSQL convert varchar with custom format to date

2350 views sql

I read a CSV with BULK INSERT. In this CSV I have dates formatted with MMM yyyy(e.g. Jan 2016).

What is the best way to convert this varchar to a date column?

So far I only found solutions using the in-built formats. None of these formats match my format. Also note that the missing day should get the value 01. This leads to following conversion:

Jan 2016 -> 2016-01-01 (format of second date doesn't actually matter. I normally use yyyy-MM-dd)

answered question

Did you try to set date format before import? SET DATEFORMAT By the way this works as you want, too SET LANGUAGE us_english; SELECT CAST('Jan 2016' AS date)

1 Answer



posted this

Have an answer?


Please login first before posting an answer.