Convert char column to datetime

Transact-SQL
1

Hi,

I received a table from a client that has dates stored in char (16) field called UpdateDate. I need to import the table into our database. The destination field is datetime and i need it to remain that way. How can I convert the char values into datetime (uk)? When i try to convert i get error Conversion failed when converting date and/or time from character string.
Example of values:
2011-06-19-14.17.07.622451
2012-02-26-15.50.37.243226
2012-02-26-15.50.37.251556
2011-09-25-16.23.57.298187
2011-09-26-16.46.19.509151
2013-04-22-11.07.23.079422

Thanks

2

Use SET DATEFORMAT

set dateformat dmy

Select 
  Id,
  PersonId,
  Case 
    When IsDate(Comment1) = 1 
    Then convert(date, cast(rtrim(Comment1) as nvarchar), 103)
    Else NULL 
  End

Know more @ https://support.microsoft.com/en-us/kb/69133

3

Thanks. I get an error Conversion failed when converting date and/or time from character string.

4

The way to solve this is to use the (slightly adapted) ISO-8601 date format that is supported by SQL Server - this format works always - regardless of your SQL Server language and dateformat settings.

https://msdn.microsoft.com/en-us/library/ms180878.aspx

5

If you are still having problems you might need to stuff a blank in position 11.

6

Still not working. What do you mean by stuffing a blank ?

7

The answer should be a bit obvious... you need to change the raw data. That also means that you should follow the best practice of loading data into a staging table instead of the final destination.

Once that's done, then you can write the formula to STUFF the "-" at character #11 with a space and replace the periods with colons. I don't recall if SQL Server will take a colon just before the milliseconds but I seem to remember it doing so. If it doesn't. then you may have to reSTUFF it with a period.

Of course, the best thing to do would be to talk to the data provider and get them to start sending only ISO dates and times consistently.

8

Here is an example:

[code]DECLARE @mt table (xd VARCHAR(50));

INSERT INTO @mt (xd) values
('2011-06-19-14.17.07.622451'),
('2012-02-26-15.50.37.243226'),
('2012-02-26-15.50.37.251556'),
('2011-09-25-16.23.57.298187'),
('2011-09-26-16.46.19.509151'),
('2013-04-22-11.07.23.079422');

SELECT xd, CONVERT(DATETIME2, STUFF(STUFF(STUFF(xd, 11, 1, ' '),14,1,':'),17,1,':'))
FROM @mt; [/code]

9

djj55's solution will definitely do the trick. The key will be in how you import. If you're trying to do a direct import, you'll continue to get failures. You HAVE to use code similar to his to make the modification prior to sending the data to the final table and a staging table will suit you very well in doing this.

10

The millisecond portion of the datetime2 format uses a period. You only want to replace the period with a colon between hours, minutes and seconds.

The end format should look like:

2011-06-19 14:17:07.622451

You also need to remove the 3rd dash between the date and time. That can be replaced with either a space or a T.

11

Heh... another feature lost thanks to the newer datatypes... DATETIME can take either a colon or a period

 SELECT CAST('2011-06-19 14:17:07:997' AS DATETIME) UNION ALL
 SELECT CAST('2011-06-19 14:17:07.997' AS DATETIME)