CAST and CONVERT not working - converting nvarChar to Date

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.

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

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)

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