Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2005
    Location
    Sharjah, UAE
    Posts
    25

    Unanswered: Converting dates.

    Kudos to y'all!!! I have this task of fixing a database table which contains dates but in a VARCHAR type column. Now I wanted to convert them to 103 format. But the problem is, some values were inserted into the database in either "dd/mm/yyyy hh:mm:ss AM/PM" or "mm/dd/yyyy hh:mm:ss AM/PM" formats since the column is VARCHAR. Is there an easy way of doing such task?
    Pinoy ako, pinoy tayo! Ipagmalaki mo...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, there is no easy way

    for example, is 04/05/2006 in dd/mm/yyyy format or in mm/dd/yyyy format?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2005
    Location
    Sharjah, UAE
    Posts
    25
    Quote Originally Posted by r937
    no, there is no easy way

    for example, is 04/05/2006 in dd/mm/yyyy format or in mm/dd/yyyy format?
    It's in either format. Some dates are in dd/mm/yyyy and some are in mm/dd/yyyy. It's an old table and I don't know for sure which date format was used for it.
    Pinoy ako, pinoy tayo! Ipagmalaki mo...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think you missed the intent of my question

    i was trying to point out that the answer to your question "Is there an easy way of doing such task?" is no, because there will always be these types of values that you just cannot decide
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What does this give you?

    SELECT * FROM Table WHERE ISDATE(DateCol)=0

    ????

    SELECT ISDATE('10/24/1960'), ISDATE('24/10/1960')
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hey brett, i got one for you in return

    what do you get for this query --
    Code:
    SELECT ISDATE('04/05/2006') as is1
         , ISDATE('05/04/2006') as is2
    mwua ha ha ha hahahaha !!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Good point, bottom line, you are hosed

    unless you have a column that identifies the format
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    eh, it's not so bad. worst case you'll convert wrong and be off by 9 months. no big deal right?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jezemine
    eh, it's not so bad. worst case you'll convert wrong and be off by 9 months. no big deal right?
    sounds like the attitude of a certain large software company which shall remain nameless...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by r937
    sounds like the attitude of a certain large software company which shall remain nameless...
    yea, they drill it into you, it takes a while to feel clean again.

    did I say 9? I meant 6. even better!
    Last edited by jezemine; 11-21-06 at 01:16.

  11. #11
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Quote Originally Posted by trojanz
    either "dd/mm/yyyy hh:mm:ss AM/PM" or "mm/dd/yyyy hh:mm:ss AM/PM" formats since the column is VARCHAR.
    How much rows are you having in your table..?

    Second thing, If you query your table, how you identify dates..? (05/04/2006 - dd/mm/yyyy or 04/05/2006 - mm/dd/yyyy )

    Consider the points given below, remember you didn't provide enough information...

    1. You can update all rows which is having 'day' more than 12. (i.e. 13/01/2006 or 01/13/2006).

    2. If you can not identify date (05/04/2006 or 04/05/2006), than date data does not make any difference to you, because in this situation you can not get correct date.

    3. Inform your higher authority & update your table, this way your new data will not be wrong.
    Last edited by rajeshpatel; 11-21-06 at 05:50.
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  12. #12
    Join Date
    Aug 2005
    Location
    Sharjah, UAE
    Posts
    25
    Quote Originally Posted by rajeshpatel
    How much rows are you having in your table..?

    Second thing, If you query your table, how you identify dates..? (05/04/2006 - dd/mm/yyyy or 04/05/2006 - mm/dd/yyyy )

    Consider the points given below, remember you didn't provide enough information...

    1. You can update all rows which is having 'day' more than 12. (i.e. 13/01/2006 or 01/13/2006).

    2. If you can not identify date (05/04/2006 or 04/05/2006), than date data does not make any difference to you, because in this situation you can not get correct date.

    3. Inform your higher authority & update your table, this way your new data will not be wrong.
    I have exactly 82,545 rows on this table and is expected to grow for a few more days since this table is still in use by one application. Currently, this application (which I made opf course) is following the dd/mm/yyyy format. This means that the SQL syntax used within the application follows this format. Therefore, the dates are inserted in dd/mm/yyyy format. As I said, this table is old and the old application that uses this table inserts date in mm/dd/yyyy format. The old application was stupid 'coz it formats date depending on th system setting and inserting it into the table as is. My only mistake is that I should've fixed the table before I started the application. For one year now, the old and the current application is inerting date values into the table as VARCHAR instead of DATETIME. Now that I'm updating the application ('coz I've managed to create it not to be dependent on the system settings), I want to start inserting date values as DATETIME so that it would work on BETWEEN statements properly as well as using SQL Server's built in functions such as DATEDIFF, DATEADD, etc. as I'll be using SQL Server Agent to execute T-SQL commands which involves dates.
    Pinoy ako, pinoy tayo! Ipagmalaki mo...

  13. #13
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Quote Originally Posted by trojanz
    I have exactly 82,545 rows on this table and is expected to grow for a few more days since this table is still in use by one application. Currently, this application (which I made opf course) is following the dd/mm/yyyy format.
    You have to take pain to replace the VARCHAR column to DATETIME column, choose the Server idle time and do it at single shot because you don't have any other option.

    There are few ways to update your DATETIME columns...

    1. You can create new table & copy all data from old table to new (using DTS).
    2. Add new column in the existing table & update it (you can write query for it & after updating remove old column).
    3. First update rows which is having 'day' more than 12, then update other rows.
    4. Don't forget to check column references.

    Note : You will get ambiguous / incorrect dates (which are below 12) because you will not identify dates between 1 to 12 (date or month).

    By converting VARCHAR to DATETIME column you can eliminate future incorrect / ambiguous data. You have to take this risk, else I didn't find any other solution...
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Do you have a time stamp on your data that would indicate whether the date was entered under the old system or under the new system? If so, you can update the dates with two separate statements.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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