Post reply

convert a column of varchar data type to datetime

  • 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

  • September 26, 2006 at 12:32 pm

    #662558

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

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

    Kindest Regards,

    Roi Assa

  • September 26, 2006 at 1:27 pm

    #662566

    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

  • September 26, 2006 at 1:40 pm

    #662568

    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

  • September 26, 2006 at 1:51 pm

    #662573

    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 

  • September 27, 2006 at 7:20 am

    #662701

    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]

  • September 27, 2006 at 10:02 am

    #662778

    Thanks!

    Well, that is what I will try then.

     

    KR

     

  • September 28, 2006 at 6:45 am

    #662954

    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

    quote 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.

  • September 28, 2006 at 12:03 pm

    #663105

     

     

    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

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

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