SQLTeam.com | Weblogs | Forums

Convert char column to datetime


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:



set dateformat dmy

    When IsDate(Comment1) = 1 
    Then convert(date, cast(rtrim(Comment1) as nvarchar), 103)
    Else NULL 

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

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

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.


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

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

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.

Here is an example:

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

INSERT INTO @mt (xd) values

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

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.

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.

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)