Results 1 to 3 of 3

Thread: Convert char date m/d/yyyy to mm/dd/yyyy?

  1. #1
    Join Date
    Feb 2009
    Posts
    27

    Convert char date m/d/yyyy to mm/dd/yyyy?

    I have a char(30) variable in the database that in some instances it has 2/2/2009 and some have 02/02/2009. I am wanting to write an sql update query that goes through and updates all instance of m/d/yyyy to mm/dd/yyyy how do I do this being that it is of type char?

    This was a result of data entry problem, different people entered data in different ways, and now that we have to go back through and change it.

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    use tempdb
    create table test (id int, date char(30))
    insert into test values (1,'2/2/2009')
    insert into test values (1,'12/2/2009')
    insert into test values (1,'02/2/2009')
    insert into test values (1,'12/02/2009')

    --Fix month part
    update test set date=case when substring(date,2,1) ='/' then '0'+date
    else date end

    select * from test

    --Fix day part
    update test set date=case when substring(date,5,1) ='/' then (substring(date,1,3)+'0'+substring(date,4,7) )
    else date end

    select * from test

  3. #3
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    or


    use tempdb
    create table test (id int, date char(30))
    insert into test values (1,'2/2/2009')
    insert into test values (1,'12/2/2009')
    insert into test values (1,'02/2/2009')
    insert into test values (1,'12/02/2009')

    update test set date = convert(varchar(30),convert(datetime,date),101)

    select * from test

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •