Convert date in Varchar field to datetime field - help please!

[NickyJay]

Hi all,

I have two fields in my table, one is a varchar field populated with dates input via a coldfusion page (there were issues with the page inputting the date in the correct format to a datetime field so this was a work around!) The format is dd/mm/yyyy.
I have a second field that a query was supposed to copy the dates in the first field but save them as dates for some reports to run from (using an age calculation, so really need some sort of date field here!)

Please can someone advise me on what is going wrong with the code i have theat did work and now is not?

UPDATE tblClient
Set DateOfBirth = CONVERT(datetime, substring(DOB, 4, 3) + left(DOB, 3) + right(DOB, 4))

I added a WHERE DateOfBirth IS NULL to see if the problem was the already populated DateofBirth field and all i get is an error:
"Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

Im a little over my head with date fields and need to try and get this working once and for all!

Thanks

Nicola

 

[drlex]

Nicola,
The error message displayed is indicating that you are providing a string which does not correspond to a date.
To check, just do a SELECT substring(DOB, 4, 3) + left(DOB, 3) + right(DOB, 4) to see what is being processed.
If the format is dd/mm/yyyy and that you are confident that 1st April 2009 is indeed "01/04/2009", then all you need to do is ensure that the string used is in a YYYYMMDD format.
No need for separators.

So on that basis, try
RIGHT(DOB, 4) + SUBSTRING(DOB,4,2) + LEFT(DOB, 2)

soi la, soi carré