Converting String todate

[IanWaterman]

I have a string date stored as

ddmmyy

When I use cast with the field = 080912

Cast(EffectiveDate as Date) I get 2008-09-12 00:00:00

I have solved this by breaking down

cast(SUBSTRING(EffectiveDate,5,2)+SUBSTRING(EffectiveDate,3,2)+SUBSTRING(EffectiveDate,1,2) as DATE)EffDate,

This gives me the correct result but looks a bit convoluted is there a more efficient way?

Thank you

Ian

 

[IanWaterman]

Posted in wrong forum moved to MS SQL Server Programming ;-)

 

[Mr Wonderful]

use convert(datetime, expression, 103)

 

[IanWaterman]

Thanks for suggestion but that still does not work

it retuns 2008-09-12 00:00:00

when correct date is 2012-09-08 00:00:00

Ian

 

[xlbo]

I think you're stuck with using an exprssion

What you have is not really a standard date format so need to break it down into it's constituent parts to make it readable as a date



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question