Converting strings to dates

[MattEustace]

Hi,

I have a varchar column with mixed date formats, some are dd-mm-yyyy and others are mmm dd yyyy hh:mmAP, these latter having been generated automatically by SQL when I changed the column datatype from datetime to varchar.

I would like to run an update script to pattern match the latter and change to the former.

I've been trying something along the lines of

update dwsubmit set authoreddate = (select day(authoreddat e))+'-'+(select month(authoredd ate))+'-'+(select year(authoredda te)) where id = 841

to try and get the format conversion correct, but this is obviously not correct. I have tried a few combinations, but have had no joy. Any ideas?

Thanks, Matt.

[Seun Ojo]

I understand ur pain man....but u r doin it wrongly. Try this:

update dwsubmit
set authoreddate = (select datepart(author eddate,dd))+'-'+(select datepart(author eddate,mm))+'-'+(select datepart(author eddate,yy))
where id = 841

op it works for u....av fun!

[Seun Ojo]

sorry i mixed it up

update dwsubmit
set authoreddate = (select datepart(dd,aut horeddate))+'-'+(select datepart(mm,aut horeddate))+'-'+(select datepart(yy,aut horeddate))
where id = 841

it should be ds way....sorry about dat...

[MattEustace]

Thanks for that - I tried something similar, but SQL is actually adding the values up to come up with 2022 (04 + 12 + 2006). I have no idea what it is doing with the '-' characters. I then tried casting each part to char, but it ignores that too!

[Seun Ojo]

i op u tried d 2nd version not d first....ol d same

try ds....

update dwsubmit
set authoreddate = select datepart(dd,aut horeddate) + '-' + select datepart(mm,aut horeddate) + '-' + select datepart(yy,aut horeddate)
where id = 841

[MattEustace]

Using what you gave me, I got to the following:

update dwsubmit
set authoreddate = cast((select datepart(dd,aut horeddate)) as char(2))+'-'+cast((select datepart(mm,aut horeddate)) as char(2))+'-'+cast((select datepart(yy,aut horeddate)) as char(4))
where id = 841

Which gives me what I want unless there is only a single digit for the month or day, in which case I get something like 4 -12-2006 which should actually be 04-12-2006.

I'm sure the convert function is a better way of doing this, but I can't figure it out!

Thanks,
Matt.