question

SqlServerNewbie avatar image
SqlServerNewbie asked

CAST and CONVERT not working - converting nvarChar to Date

Hi all - I really hope you guys can help. I am aware that this question is asked alot, and I have tried using the various solutions on here as well as other sites, but either there is something I am missing or not taking into consideration. Basically I am updating one table with another. Table A is the one I am trying to update - the field is SubscriberStartDate and is a date type with format yyyy-mm-dd Table B is the updating table - the field is ActivationDate and is a nvarChar type with format dd/mm/yyyy With all the various ways I have tried (shown below) I get the error: Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string. I have tried the following simple select to try test the conversion logic and all variations on the type code (103, 106, 123 etc) as well as type (date, datetime, shortdate) select CAST(ACTIVATIONDATE AS DATETIME) FROM B select convert(datetime,ACTIVATIONDATE,106) FROM B select convert(datetime,((left(rtrim(ltrim(ActivationDate)),8))+' '+'00:00:00.000'),103) from B select convert(datetime,((left(rtrim(ltrim(ActivationDate)),8)),103) from B select convert(datetime,(datepart(yyyy,(subString(rtrim(ltrim(ActivationDate)),7,4)))+'-'+ datepart(mm,(subString(rtrim(ltrim(ActivationDate)),4,2)))+'-'+ datepart(dd,(subString(rtrim(ltrim(ActivationDate)),1,2)))),103) from B select datefromparts(subString(rtrim(ltrim(ActivationDate)),7,4))), subString(rtrim(ltrim(ActivationDate)),4,2))), subString(rtrim(ltrim(ActivationDate)),1,2)))) from B
datetimedate-functions
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
The error simply means that (at least) one of the values can't be converted into a **valid** date - the format might be spot on, but if you had a string of '13/13/2012' - then there's no such month as 13. You might want to try and narrow down the issue, for example, check for invalid month values: select ActivationDate from YourTable where subtsring(ActivationDate, 4,2) > 12 it gets more complex when you want to check day values, as it obviously depends on the month too, eg. 31 is valid for Jan but not for Feb. Try the obvious errors first like days > 31, or year > 2012 (if that's valid)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
This is where I always hate storing data in a data type other than actually required. Especially when it comes to dates. In your situation, these all statements could fail depending upon the DATEFORMAT/LANGUAGE settings and also due to the fact that you are only taking 8 characters from the NVARCHAR column. For e.g. select CAST(ACTIVATIONDATE AS DATETIME) FROM B will prompt the error if your DATEFORMAT is not set to DMY. Try the following instead and I am pretty sure, no error would be prompted SET DATEFORMAT DMY select CAST(ACTIVATIONDATE AS DATETIME) FROM B GO For first 8 characters with use of LEFT function i.e. select convert(datetime,((left(rtrim(ltrim(ActivationDate)),8))+' '+'00:00:00.000'),103) from B go try this instead SET DATEFORMAT DMY select convert(datetime,((left(rtrim(ltrim(ActivationDate)),10))+' '+'00:00:00.000'),103) from B go I hope, now you would be able to sort out why it was not working. Moreover, this is the best time to consider storing values in their respective data types. It will save you from such terrible mess-ups and also it would yield more throughput.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
First as @Usman mentioned, use the `SET DATEFORMAT DMY` to set proper data format for conversion. If even after setting proper DATEFORMAT you will receive errors during CASTING, you can use below query to find out exactly what record(s) is/are causing the problems. --Set format to be used for date (the format of source data) SET DATEFORMAT DMY SELECT ActivationDate FROM YourTable WHERE ISDATE(ActivationDate) = 0
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image Pavel Pawlowski commented ·
And in case of SQL 2012 we can use also the TRY_CONVERT :-) SELECT ActivationDate, TRY_CONVERT(datetime, ActivationDate, 103) AS ConvertedDate FROM YourTable In that case the ConvertedDate will have NULL value in cases when conversion fails. To filter out the rows which are causing conversion error we can use: SELECT ActivationDate, TRY_CONVERT(datetime, ActivationDate, 103) FROM YourTable WHERE TRY_CONVERT(datetime, ActivationDate, 103) IS NULL There is no need to set the `DATEFORMANT` as the format is part of the TRY_CONVERT.
1 Like 1 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
+1 Why do I always forget about this function!
0 Likes 0 ·
SqlServerNewbie avatar image
SqlServerNewbie answered
Thanks guys! That's great....and as newly appointed systems analyst charged with maintaining data, this may well be a worthy 'first task'. Additionally, thanks for that nice little tip Pavel - turns out the Activation Date contained some spaces for some of the records instead of NULL and these where causing the problem. Out of interest, on the dev environment, after I excluded the records with the spaces, the following worked: a.SubscriberOfferStart = (subString(rtrim(ltrim(b.ActivationDate)),7,4)+'-'+subString(rtrim(ltrim(b.ActivationDate)),4,2)+'-'+subString(rtrim(ltrim(b.ActivationDate)),1,2))
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.