0

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')
Anton Belev
  • 11,963
  • 22
  • 70
  • 111

2 Answers2

1

You can use stuff to remove the at and convert using style 103 (assuming dd/mm/yyyy) or 101 (assuming mm/dd/yyyy):

DECLARE @Date varchar(30) = '11/04/2017 at 08:24:52'

SELECT CONVERT(datetime2, STUFF(@Date, 12, 3, ''), 103)

Result:

    11.04.2017 08:24:52
Graham
  • 7,431
  • 18
  • 59
  • 84
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
1

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
iamdave
  • 12,023
  • 3
  • 24
  • 53