Link to home
Start Free TrialLog in
Avatar of csetzkorn
csetzkorn

asked on

transform varchar to datetime

Hi,

I have a column which contains varchar(max) entries like this:

2/5/2002 00:00:00

Is there a easy way to tranform this column to the datetime format?

Many thanks.

Best wishes,

C
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

hi, try this

cast(columnname as datetime)
Avatar of csetzkorn
csetzkorn

ASKER

That does not work as the month and day is not in the 'right' order.

Usually the datetime looks like this:

1998-03-20 00:00:00

This varchar is in this format:

day/month/year 00:00:00

I could do some 'complicated' transformation by extracting the day and month etc. from the string (using '/' as a separator) but that seems to be too cumbersome.

BTW I would like to change the actual datatype in the table as well.

C
how about this one?

convert(datetime,columnname,103)
Thanks. That works fine. Could you possibly be so kind and remind me how to update the datatype of the table using this method?

Thanks.

C
ASKER CERTIFIED SOLUTION
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ok. thanks. thought there is an easier way.

C
i have found this
http://support.microsoft.com/kb/173907

try to set the default date format to day/month/year first before changing col type

SET DATEFORMAT dmy
ALTER TABLE tablename ALTER COLUMN columnname datetime