Converting varchar to a datetime datatype

  • Dear Friends,

    Is it possible to convert or cast a varchar datatype field to datetime.

    My table contains a field dateofbirth with varchar datatype how can it be converted to a datetime field without affecting the data.

    Thanks.

  • This works fine for me (the bit in purple is the bit you need)...

    --data

    set nocount on

    create table #t1 (dateofbirth  varchar(30))

    insert #t1

              select '1 jan 2000'

    union all select '24 jul 2006'

    --calculation (alter the table column)

    select * from #t1

    alter table #t1 alter column dateofbirth datetime

    select * from #t1

    drop table #t1

    /*results

    dateofbirth                   

    ------------------------------

    1 jan 2000

    24 jul 2006

    dateofbirth                                           

    ------------------------------------------------------

    2000-01-01 00:00:00.000

    2006-07-24 00:00:00.000

    */

    Make sure you do "select dateofbirth, cast(dateofbirth as datetime) from mytable" first and are happy with the results (i.e. it doesn't error and the values are as you'd like).

    And make sure you have a backup in case it all goes wrong

     

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • Thanks for your reply....

    I tried using cast and convert but did't  work.

     

     

     

  • Okay - that sounds like there's some data in there which is not valid datetime data.

    Try running...

    select dateofbirth from mytable where isdate(dateofbirth) = 0

    If there are some results, you'll have to fix that data first...

     

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • If you are sure the format of your varchar, in your table, is always the same or implicitly convertible to a date (which it seems like it is not in your case), you are in luck. Then it is easy to convert the data as soon as you figure out the format and the appropriate conversion routine.

    Give us an example of the format used in the table and we might be able to help you more.

    Hanslindgren

  • Topic "Cast and Convert" in BOL must be really helpful for you.

    _____________
    Code for TallyGenerator

  • For datetime you better use convert instead of cast becasue for convert you can specify the format of your character data. See BOL for convert and there the style parameter



    Bye
    Gabor

  • But Shabu doesn't want to format data?

    He wants to change the datatype of an existing varchar column to datetime.

    He's got issues with doing that seemingly because there is some data in there which is not compatible. He should find and fix that data with the help of ISDATE, and then just alter the column.

    Shabu - How are you getting on?

     

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • The question is not about formating the data.

    If you have a varchar column of format mm/dd/yyyy then you have to use convert(datetime, DateCol, 101) or if you have a format yyyy/mm/dd then you have to use convert(datetime, DateCol, 111) and so on



    Bye
    Gabor

  • Dear Friends,

    RyanRandall is correct my problem is with the data in the table

    the format of the date in the table is a mixture of mm/dd/yyyy and mm/dd/yy format.

    IsDate(DOb)=0  returned  many  records.

    Thanks for your valuable information

     

     

     

  • is weired that someone mixed data like that...

    Take a look at SET DATEFORMAT and run that query again the problem might be that you preferences are in different format than the saved data so the conversion fails.

    data is saved in MM/DD/YYYY and your preferences are DD/MM/YYYY causing some records to fail


    Kindest Regards,

    Vasc

  • Hello!

    This leads to quite a problem parsing your data. But if you know that these two are the ONLY types of dates inserted (somehow I would verify it VERY carefully) then I have a solution. (Once again be sure these are the only two formats and that no data in the form mm/dd/yy has been inserted as yy/mm/dd, example: what date does 01/02/03 correspond to? Or 03/02/01?)

    Anyways, if the data is of the mm/dd/yyyy or mm/dd/yy format you can use DATETIMEFORMAT (as Vasc suggests) to convert properly or use the LEN(myVarcharDateColumn) to decide what other tactic you could use (UPDATE, INSERT INTO a new table with a DateTime column or creating a new datetime column and inserting into that).

    Hint on SET DATETIMEFORMAT (look at the output of the last one):

    SET DATEFORMAT DMY

    SELECT ISDATE( '12/31/2077')

    SELECT ISDATE( '12/31/77')

    ------------------------------

    Output:

    0

    0

    SET DATEFORMAT YMD

    SELECT ISDATE( '12/31/2077')

    SELECT ISDATE( '12/31/77')

    ------------------------------

    Output:

    1

    0

    SET DATEFORMAT MDY

    SELECT ISDATE( '12/31/2077')

    SELECT ISDATE( '12/31/77')

    ------------------------------

    Output:

    1

    1

    Hope this might help!

    Hanslindgren

  • The real problem is that no matter what anyone does, you will not be able to differentiate between the two formats if both the day and month are <= 12.  For example, someone born on 01/06/1970 (dd/mm/yyyy) will likely be interpreted as 01/06/1970 (mm/dd/yyyy) no matter which of the fine methods posted are used.  There's simply no way to tell which format was used by looking at the data.  Sure, you can make it convert to the datetime datatype, but some of the dates will be incorrect.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • If you're using the date for display this is how I accomplished that:

    CONVERT(DATETIME,SUBSTRING(b.duedate,5,2)+ '-' + SUBSTRING(b.duedate,7,2) + '-' +  SUBSTRING(b.duedate,1,4),101) + 30 AS duedate, 

    duedate in my case is a varchar(8).  Just a thought.  Haven't run into an issue where I'll get an invalid date, however I think you'll just get a NULL in the record if it doesn't format properly.

    Thanks,

  • But that only means that your duedate varchar column always contains dates in the format YYYYMMDD...

    Here the problem is that it is different for different records in the database.

    Why do you store it as a VARCHAR(8) and not a CHAR(8) (saving space in the DB, makes more sense, does not need to keep track of stringlengths for every record).

    Better yet. Why not a date? A date should always be a date, wouldn't we call it something else if not?

     

Viewing 15 posts - 1 through 15 (of 17 total)

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