Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2004
    Posts
    5

    Unanswered: Date conversion problem - Desperate!

    I have quite a few records in the date fields that have been incorrectly converted or recorded a few years back. They look like this: "2/28/2020 11:52:00 PM" The year is always way in the future and "11:52:00 PM" time is exactly the same in all of them.

    They should insted look more like "4/10/2002 6:13:03 PM", which is when the events actually happened.

    I am guessing this happened when the field type was changed or the data was migrated to MS SQL Server.

    Is there a way to restore them back to original form?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Godfather
    Is there a way to restore them back to original form?
    possibly, yes

    what database are they in? MySQL or SQL Server?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2004
    Posts
    5
    They are in SQL Server now.

    Thank you.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    when you say that "2/28/2020 11:52:00 PM" should look more like "4/10/2002 6:13:03 PM", how much more?

    can you say exactly what to change?

    e.g. change feb to apr, change 28th to 10th, ... etc. ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2004
    Posts
    5
    I am certain of what the date should be for all records. In this case it should definitely be "4/10/2002". The only unknown is the time. I made up "6:13:03" to illustrate (I kinda know approximately at what time these entries happened). And thats exactly why I am trying to restore the data. Approximately is not good enough. The times have huge implications for the company.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you know how to change '2/28/2020' to '4/10/2002', right?

    subtract 6533 days

    you'll need something similar for the times
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2004
    Posts
    5
    Maybe I was not very clear. The times are showing all the same (11:52:00) but need to be different. But the correct date is known. Here is a sample.

    What we have in db ----- Correct date ----- Correct time
    4/29/2020 11:52:00 PM ----- 4/30/2002 ----- ?
    4/29/2016 11:52:00 PM ----- 5/1/2002 ----- ?
    10/30/2013 11:52:00 PM ----- 5/2/2002 ----- ?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i cannot begin to guess what the correct times should be

    why can't you just leave them as 11:52?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you don't know the times, then leaving them at a consistently arbitary value would be beneficial - you know that records with that value were corrupted, and the itme cannot be relied on. It may make more sense to make them NULL, providing you don't use the time in any calculations.

    failing that you are going to have to go back to the original conversion process and work out how a valid date in your source became mangled in the conversion. Having worked that out then, oand only then will you be able to do understand how to fix the problem. it may even require running an update query setting just the date and time, providing of course you have a valid means identifying matching rows.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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