Post reply

Format Date in SQL Table

  • January 17, 2008 at 3:19 pm

    #182201

    i have a field with datatype nvarchar but it is going to be converted to datetime.

    Since it was in nvarchar the user had entered dates in different format like

    01/2/2007

    1/2/2007

    01/02/07

    and so on.

    i want to convert all dates to be in mm/dd/yyyy format.

    Can i do it in thru sql statement for the entire field value? if so how do i do it.

    Thanks

  • January 17, 2008 at 3:36 pm

    #769483

    select convert(varchar(20), cast(datefield as datetime), 101)

    from dbo.table

    That should do the trick for you. Just replace "datefield" with the actual column name and "dbo.table" with the actual schema and table.

    If that gets you what you want, then convert it to an update statement:

    update dbo.table

    set datefield = convert(varchar(20), cast(datefield as datetime), 101)

    Before you run that, you might want to run:

    select datefield

    from dbo.table

    where isdate(datefield) = 0

    That will give you all the rows where the field can't be converted into a datetime field automatically. If there are any, you'll need to clean those up first.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 17, 2008 at 9:03 pm

    #769534

    Changing the format of the "date" column is just a repetitition of the original mistake... for more reasons than will fit in this meager window, you must NEVER store dates or times as anything but the DATETIME datatype... period.

    --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)

  • January 18, 2008 at 9:00 am

    #769732

    He's already mentioned that the column is being changed to datetime. He just wants to reformat it first (didn't say why). So, he's on the right track and has the right end result, just wasn't sure of one of the steps on the way.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 18, 2008 at 9:52 am

    #769774

    select convert(varchar(20), cast(datefield as datetime), 101)from dbo.table where datefield is not null

    i replaced datefield to my column name which is of nvarchar(50) containing dates and the table name.

    but i get the below error.

    Conversion failed when converting datetime from character string.

  • January 18, 2008 at 10:59 am

    #769798

    GSquared (1/18/2008)

    He's already mentioned that the column is being changed to datetime. He just wants to reformat it first (didn't say why). So, he's on the right track and has the right end result, just wasn't sure of one of the steps on the way.

    Ah crud... I gotta learn to slow down and read the whole post. Thanks for the correction.

    --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)

  • January 18, 2008 at 11:18 am

    #769805

    Thats because you have bad data in it.

    -Roy

  • January 18, 2008 at 12:14 pm

    #769837

    keywestfl9 (1/18/2008)

    select convert(varchar(20), cast(datefield as datetime), 101)from dbo.table where datefield is not null

    i replaced datefield to my column name which is of nvarchar(50) containing dates and the table name.

    but i get the below error.

    Conversion failed when converting datetime from character string.

    That's because some of the rows (maybe 1, maybe more) aren't formatted correctly to be dates.

    To find which ones:

    select datefield

    from dbo.table

    where datefield is not null

    and isdate(datefield) = 0

    The "isdate()" function will find the ones that can't be converted to dates, and you can clean them up.

    Since it's running a function on a column in the Where clause, it might be a little slow, but it shouldn't be too bad unless the table has millions of rows, and it's only for this one use, so it's not a big deal.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 18, 2008 at 1:19 pm

    #769865

    That Worked. Thanks.

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

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