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)
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.
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
For first 8 characters with use of LEFT function i.e.
try this instead
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.
First as @Usman mentioned, use the
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.
answered Jun 13, 2012 at 10:08 AM
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:
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)
answered Jun 13, 2012 at 07:44 AM
Kev Riley ♦♦
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))
answered Jun 13, 2012 at 12:20 PM