x

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
more ▼

asked Jun 13, 2012 at 07:35 AM in Default

SqlServerNewbie gravatar image

SqlServerNewbie
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.
more ▼

answered Jun 13, 2012 at 09:36 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Jun 13, 2012 at 10:08 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

+1 Why do I always forget about this function!
Jun 13, 2012 at 11:22 AM Kev Riley ♦♦

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.
Jun 13, 2012 at 12:53 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

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)

more ▼

answered Jun 13, 2012 at 07:44 AM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

(comments are locked)
10|1200 characters needed characters left

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))
more ▼

answered Jun 13, 2012 at 12:20 PM

SqlServerNewbie gravatar image

SqlServerNewbie
0 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x94
x43

asked: Jun 13, 2012 at 07:35 AM

Seen: 3654 times

Last Updated: Jun 13, 2012 at 12:53 PM