convert a column of varchar data type to datetime

Question

Post reply

convert a column of varchar data type to datetime

Karthika Raman

SSCrazy

Points: 2520
More actions
September 26, 2006 at 11:15 am

#60868

Hi,

What is the best way to go about converting a column that has dates and is of varchar datatype to datetime datatype.

Thanks
KR

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Roi Assa SSChasing Mays Points: 611 More actions · Answer 1

Can you post an example to see the way you store it.

dd-mm-yy / mm-dd-yy etc...

Kindest Regards,

Roi Assa

Karthika Raman SSCrazy Points: 2520 More actions · Answer 2

Well since it in in varchar format it may not be all in the same format but for most part it is in mm-dd-yy

Thanks

KR

Sameer Raval Hall of Fame Points: 3214 More actions · Answer 3

FOR format mm/dd/yy ( 06/26/06 )

Select convert (datetime, '6/25/2006')

will convert your varchar to datetime. Instead of '6/25/2006' you can use variable provided you feed that variable with mm/dd/yy.

Kindest Regards,

Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com

Karthika Raman SSCrazy Points: 2520 More actions · Answer 4

Thank you!

I am looking for more the best method of going about doing such a process - I was thinking that maybe I have to create a new column of datetime and then using CAST or Convert functions I could populate the new column with the data, cleanup any errors and then delete the old column. I am wondering if there is a better process out there to do this type of thing.

Thanks

KR

Luke L SSC-Dedicated Points: 35547 More actions · Answer 5

It seems like youa re on the right track. Create the new column copy the data using convert and then you'll have to scrub the data for any errors for times when people entered the incorrect data, or incorrect data format.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Karthika Raman SSCrazy Points: 2520 More actions · Answer 6

Karthika Raman

SSCrazy

Points: 2520

September 27, 2006 at 10:02 am

#662778

Thanks!

Well, that is what I will try then.

KR

David Burrows SSC Guru Points: 65144 More actions · Answer 7

Just some observations

You will not be able to update the new column in a set based update unless all of your dates are valid or SQL will stop at the first invalid date with an error

You will need to check the date first

SET DATEFORMAT MDY

UPDATE

SET [newcolumn] = CAST([coldcolumn] as datetime)

WHERE ISDATE([coldcolumn]) = 1

you need to set DATEFORMAT to make sure you convert the correct dates

Well since it in in varchar format it may not be all in the same format but for most part it is in mm-dd-yy

How will you know which format?

eg is 01/03/2006 1st Mar or 3rd Jan

Far away is close at hand in the images of elsewhere.
Anon.

Karthika Raman SSCrazy Points: 2520 More actions · Answer 8

Thanks so much for this input. I was just considering it this morning, so I am glad to have this. I guess I might need to modify the process depending on the number of variations in the data etc - will try to see if using excel to do the data scrubbing makes it any easier- I don't have much experience using excel, but one of my collegues was suggesting to try it out.

thanks

KR