Adding one year to a date 3

[german12]

What is a quick way to add one year to a date?
Example:
In a table the date-field "Datum" contains 08.06.2023 (german dd-mm-yyyy)
I want to replace all fields in a table containing 2023 into 2024
So for the example the data should be 08.06.2024 then.
It is a table with birthdays.

I know that a date can be splitted into day(date()), month(date()), year(date())
and it would be possible to create a string of all this, but then the days < 10 have to get a "0"
and the string has to be converted into a date, and the string has to have "."

Adding 365 days to a date? Then a leap year has to be considered.

Sounds all too elaborate to me.

In a replace-command only dates at the end of February has to be considered because of an eventual leap year.

I can imagine that there is an easier way.

Can you help?

Thanks
Klaus



Peace worldwide - it starts here...

 

[Mike Gagnon]

?GOMONTH(DATE(),12)



If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[german12]

Thank you very much Mike Gagnon.
That is exactly what I wanted. (I forgot the gomonth-function)
Klaus


Peace worldwide - it starts here...

 

[Chris Miller]

Well, leap days are still problematic.

As in this year 2023 you couldn't have a birthday reminder stored for 29th of February, GOMONTH() would not get to 29th February next year, even though next year is a leap year and that date 2/29/2024 exists. So it's also a problem in years before leap years, if you have someone born in a leap year on the leap day.

It might not be a problem for you. But as you already mentioned the leap day corner case, if you want a solution that takes care of this date, GOMONTH also doesn't solve it perfectly.

There's also the non-technical problem if you have a reminder on 1st of March this year and don't know whether that was for someone actually born on 1st of March or on 29th February. That information would need to be in the date of birth, if you have that.

Well, and if you have the actual date of birth (DOB) you can go from there to whatever year with GOMONTH and get the leap day in leap years. But even then, for normal years you get 28th of February instead of the 1st of March. If you want to get the 1st of March in normal years for someone born on a leap day, then you still need to adjust that case.

So for a perfect solution, you need to use DATE() with the date parts. The good news is DATE() will give you an empty date for trying 29th of February in normal years, it doesn't error and you can use EVL() or IIF() to adjust the empty result with March, 1st. Another advantage of DATE() is that it doesn't need any string manipulations you don't like to do, you just have the three parameters year, month and day, numerically.

Chriss

 

[german12]

Thank you Chriss,
I am glad that I had started my question.
At first I thought that were too simple....you never stop learning.

Klaus



Peace worldwide - it starts here...

 

[Chris Miller]

Well, the question is what data do you have at hand aside from the date of reminders. They will mostly be the birth date, except for 29th February, as said.

If you have date of birth as DOB you can use GOMONTH(DOB,12*(destinationyear-YEAR(DOB))). The only leftover problem is getting 28th of February from a DOB at 29th of February in normal years.

If you have the information of month/day without a year, then you can use DATE(destinationyear,month,day) and when getting the empty date you know that's because that day doesn't exist in the destinationyear, which can only be the case for the leap day, unless you have totally wrong and incorrect birthday information. So if you can rely on good birthday information the empty result of DATE(destinationyear,month,day) would be adjusted to DATE(destinationyear,3,1). You can also check the validity of a month/day by checking DATE(2000,month,day). That should neither error nor be empty.

Chriss.

 

[Mike Lewis]

Klaus,

I wouldn't spend too much time worrying about leap days. The solution given by Mike Gagnon will nearly always be appropriate.

If you apply GMONTH() to 29 February in a leap year, the result will be 28 February in the following year. In most countries and jurisdictions, that is correct. For example, in the USA, 28 February is considered one year later than 29 February for legal purposes. Someone who was born on 29 Feb will have their birthday in non-leap years on the 28 Feb (for legal purposes). That's not universally true. Here in Scotland, their birthday would be on 1 March. But it's true in most cases.

But my point is that none of this is likely to have any significance as far as your application is concerned.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[german12]

I think I need to be a bit clearer - what my goal is.
The table below should show me
how many days it will be before an event (column start) in future occurs.
See column "daysleft"
That is easy to be calculated with
REPLACE ALL daysleft WITH start+1-_diarydate

So I can see every day whether an appointment is already urgent or not.
Events that are no longer happening can easily be deleted next year, because they affect specific groups see column "group".
Then the list can be reused and birthdays, wedding-days or other fixed dates should remain in the list, the only show the next year in the "start"-column

All of that would be done by the GOMONTH function.

But if someone's birthday is on February 29th or it's a wedding anniversary (i.e. dates that you always want to see as they are - up to now there is nothing), then it is a mini-problem, as you can not continue the procedure with GOMONTH (throwing away the old year and replace it with old-year+1, as that date is not visible anymore,
I agree with Mike Lewis - it is not a significant problem and perhaps a long time no problem....


It's probably easiest to maintain "leap" appointments like this in
an extra column manually.


Here is my list.

Structure:

Klaus





Peace worldwide - it starts here...

 

[Chris Miller]

...you can not continue the procedure with GOMONTH (throwing away the old year and replace it with old-year+1, as that date is not visible anymore.

No, that's not true, GOMONTH(date,12) always produces a date, this never has an "invisible" date, or empty date, if you mean that.

As Mike explained the 28th of February is a correct alternative date for some things, legally, but he also gave the exception in Scotland. That's your only problem, not that of an empty date. You might got confused by me mentioning DATE() could produce an empty date - which is a date not "visible", well it is displayed as \ \ or I guess as . . in German format.

But as I thought you just have some birthday or other anniversaries of this year, not the date of birth of a person. Even less so as it also involves other dates of appointments or events which have no root date and also wont always repeat yearly or be at the same date next year, like vacations. From that point of view I agree with Mike and just take the easy win and use GOMONTH(start,12) to compute next years date.

You can always calculate the date difference, that's true. And that's also a reason to not store daysleft in the table, you can always just calculate start-DATE() so daysleft is redundant information, which also outdates each day. Display a query with Select *, start-DATE() as daysleft From thetable.dbf and you have that column available. Then it's just a matter of the grid design to have it in the column you want it in, it doesn't have to be in order of the fields of the table or the query.

Note _diarydate is DATE() by default, but when you activate window calendar and pick another date, that becomes _diarydate. If you calculate start-_diarydate you get the daysleft to start from that picked date, not from today.

Chriss

 

[Chris Miller]

From the data structuring point of view, a group should be made a separate table to which you refer to by ID only.

Eg groups could be

[pre]id groupname
1 Schach
2 Malkurss
3. Geburtstag
[/pre]

and so on. That can easily be extended when you enter something new, but you mainly only need to pick from that list, to assign it to a group.

You still can order by groupname simply by query: Select * From events left join groups on events.groupid = groups.id order by groups.groupname.

Queries are your views, you could literally use views only to display data and use dbfs only to store them in the normalized non redundant format. In very strict rule about database design tables are rarely used as the only source of a listing, report, you always query multiple tables joined to what you want to view, that's also a reson views ar called that, not only because they are point of views on the data. And views then also become your comfortable replacement of using dbfs, you just USE views or put them in the DE of forms, so you don't need to repeat writing queries and do, as if that view query was a table of your database, while the composition of fields only exists in the view query definition.

Chriss

 

[german12]

Thank you Chriss - for your always helpful suggestions.
I will implement them like this....
Klaus

Peace worldwide - it starts here...