Help with date logic

[gkick]

Win10 LO6.4.7.2 HSQL2.51

Hi,

Reworking my one year old first attempt of an alert functionality. Basically I have Documents and Contracts having an expiry date, Birthdays which are repeats, People who havent been in contact since, Accounts that need to be renewed etc.

When I did this first I shy-ed away from a union query as no can sort and read only. Now I actually can sort a union indirectly by creating a view and then a query on that view for the form.

The form has fields like AlertType, DueDate, RemindMeDaysBefore and DaysLeft amongst other things.
My only problem I can not figure out how to get the DueDate for a persons next birthday as the actual DOB is static, but birthdays happen every year.
I have troubles getting any result with datediff which might be the wrong function to do this.

So basically I need the next birthday as date regardless if the bd is in the current or the next year.
Hope this makes sense

Thanks for any ideas

Thank you Ratslinger, I edited this post as I cant add images to a comment…

Thanks @ Ratslinger, works if the nbd field is populated first with the DateOfBirth, yet I have
difficulties getting the id and an alias for C1 into the output

I did something similar about a year ago using TO_CHAR( DATEADD( ‘year’, 1, “Date” ), ‘DD/MM/YY’ )
but how would I convert this string back into a real date

[Ratslinger]

Hello,

As much did not seem relative, answer is just focusing on:

So basically I need the next birthday as date regardless if the bd is in the current or the next year.

This works for me:

Select  Case When "BD" <  RIGHT(Cast(TODAY() as VARCHAR(10)),5)
                                 Then YEAR(TODAY()) +1 || '-' || "BD"
                                 Else  YEAR(TODAY()) || '-' || "BD" END
 From (Select  RIGHT(Cast(DATE1 as VARCHAR(10)),5) "BD" from DATETEST)

[gkick]

Thanks @ Ratslinger, I have edited the original post

[Ratslinger]

This works:

Select  DATEID, Case When "BD" <  RIGHT(Cast(TODAY() as VARCHAR(10)),5)
                    Then YEAR(TODAY()) +1 || '-' || "BD"
                    Else  YEAR(TODAY()) || '-' || "BD" END AS "Next Birthday"
 From  (Select DATEID, RIGHT(Cast(DATE1 as VARCHAR(10)),5) "BD" from DATETEST)

Edit:

BTW, YYYY-MM-DD is a real date. It is in fact the standard for storing in the database.

[gkick]

@ Ratslinger Thank yo very much, you are fantastic !

Yeah, can be a real health hazard from European Aussie brain.

[Ratslinger]

@gkick,

If you display that YYYY-MM-DD field in a form with a date control, you can display in format you want. Just tested to show MM/DD/YY. Should be able to display what you need without further conversion.

[gkick]

Thanks, where do you get your patience from ? Teaching background?

[gkick]

@ Ratslinger, still need to convert the result to a legit date because the union comes up with incompatible datatype as all the other tables dates are datefields

[Ratslinger]

Should be able to cast as date:

CAST('2021-01-01' AS DATE)

[gkick]

@ Ratslinger Thank you, working perfect !

[Ratslinger]

@gkick,

Just a friendly note. You are entering user name references incorrectly. There should be no space between the @ and the user name. Note my references to yourself. Correctly entered will present notification to the user and it is also a link to the users profile.

[gkick]

@Ratslinger, oops sorry, thanks for letting me know