Converting strings to dates

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(authoreddate))+'-'+(select month(authoreddate))+'-'+(select year(authoreddate)) 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.I understand ur pain man...but u r doin it wrongly. Try this:

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

op it works for u...av fun!|||sorry i mixed it up

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

it should be ds way...sorry about dat...|||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!|||i op u tried d 2nd version not d first...ol d same

try ds...

update dwsubmit
set authoreddate = select datepart(dd,authoreddate) + '-' + select datepart(mm,authoreddate) + '-' + select datepart(yy,authoreddate)
where id = 841|||Using what you gave me, I got to the following:

update dwsubmit
set authoreddate = cast((select datepart(dd,authoreddate)) as char(2))+'-'+cast((select datepart(mm,authoreddate)) as char(2))+'-'+cast((select datepart(yy,authoreddate)) 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.

converting newId()

Hi there!
Is there any way to convert generated newId() hexa into an int ?
for example, the first two char into an int which won't be greater
than 255
I've searched for 'convert' or 'cast' but i found nothing...
any idea?
thanks a lot
Vince.Why not just use RAND() ?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Vince .>" <vincent@.<remove> wrote in message
news:qfp541pgb9rfon1nk7cblubersvl3ki0cc@.
4ax.com...
> Hi there!
> Is there any way to convert generated newId() hexa into an int ?
> for example, the first two char into an int which won't be greater
> than 255
> I've searched for 'convert' or 'cast' but i found nothing...
> any idea?
> thanks a lot
> Vince.|||If you just want to convert newid() to int, try:
select convert(int, convert(varbinary, newid()))
"Vince .>" wrote:

> Hi there!
> Is there any way to convert generated newId() hexa into an int ?
> for example, the first two char into an int which won't be greater
> than 255
> I've searched for 'convert' or 'cast' but i found nothing...
> any idea?
> thanks a lot
> Vince.
>