×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

i have a table that has the wrong year in a date field

i have a table that has the wrong year in a date field

i have a table that has the wrong year in a date field

(OP)
Hi everyone
i have a table and i opened it in vfp 9.0
i have a field named "day" and this is a date field type
some values under the "day" are appearing as 02/26/1918 and i know the year suppose to be 2018
can anyone please pointing how can i scan this table and where ever i find 1917 or 1918 to be able to replace the year accordingly to 2017 and 2018 ?
thanks in advance

RE: i have a table that has the wrong year in a date field

Try something like this:

SELECT TheTable
REPLACE Day WITH GOMONTH(Day, 1200) FOR YEAR(Day) = 1917 OR YEAR(Day) = 1918


Note that the above answers the exact question that you asked. It will update dates in 1917 and 1918 to 2017 and 2018. But I suspect you also want to know how to prevent this situation from happening in the first place. If so, read the Help topic on SET STRICTDATE and SET CENTURY ... ROLLOVER.

By the way, Day is not a good choice for a field name, as it is also the name of a built-in function.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: i have a table that has the wrong year in a date field

There's not much more to say. Knowing there is a YEAR() and GOMONTH functions you can also simply find out any dates outside of a range of valid years for your data, if you search for it. Also Date() heps as reference for today, so you could find any future dates not valid, too:

Everything outside the last 20 years up to today:

CODE

SELECT * FROM TheTable Where Day<GOMONTH(DATE(),-240) OR Day>Date() 

I once migrated data, that had a long history starting in Foxpro DOS tables, where dates had century errors even more off, going back to the first century. And though DOS is old, it's not that old.

Bye, Olaf.

RE: i have a table that has the wrong year in a date field

(OP)
Tnanks a lot Mike, yes correct day it is not good name for that field, the problem is that this field name it is all over a bunch of report someone before me defined like that, so a lot of work to do to replace everywhere that field named DAY
will look into those commands
THANKS A LOT

RE: i have a table that has the wrong year in a date field

(OP)
Great olaf , thanks so much, that is a good trick

RE: i have a table that has the wrong year in a date field

It appears these dates were entered or added in shortened form such as "18" rather than 2018. The SET CENTURY setting should avoid defaults to 19XX that would be outside the usual range. However, don't force every 2 digit year to 20XX, there may be times you want to enter an age or event and if the person is 100 years old you would want 1918 rather than 2018. You may ponder whether you want to enforce 4 digit year entries on at least some of your data entry points.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close