Search code examples
sqlsql-server-2008datetime2

Converting a custom date string into SQL datetime2

I have to load a CSV file into a temp table in SQL server 2008. There is a column in the CSV file which contains a date string formatted like this 11/04/2017 at 08:24:52. How can I parse this string and insert it into a datetime2 column?

The following results in expected conversion error - Conversion failed when converting date and/or time from character string.

  create table #temp
  (
    date_col datetime2(2),
    some_id varchar(20)
  )

  insert into #temp(date_col , some_id )
  values ('11/04/2017 at 08:24:52', '2323434')
Solution

  • You just need to remove the at along with one of the spaces and then tell SQL Server which format you have your days and months in:

    select convert(datetime2, replace('11/04/2017 at 08:24:52',' at',''),103) -- 2017-04-11 08:24:52.0000000
      ,convert(datetime2, replace('11/04/2017 at 08:24:52',' at',''),101) -- 2017-11-04 08:24:52.0000000