×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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?

CODE


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

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

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é

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close