firebird how i construct a date from day, month ad year

[Luigi Siciliano]

Hello,

  I need in a stored procedure of firebird v. 2.5.9 to compare a date
and if needs construct a date like Delphi EncodeDate(dd, mm, yyyy).

Example:

  I have a DateField with a  '23.05.2025' value i need to change it in
'01.01.2025'

  I have a DateField with a  '03.11.2025' value i need to change it in
'01.07.2025'

In a stored Procedure I try:

begin
  Day = Extract day from Datefield;
  Month = Extract month from Datefield;
  Year = Extract year from Datefield;
 if Month < 7 then
    datefield = *EncodeDate(1, 1, year)*
  else datefield = *EncodeDate(1, 7, year)*;
end;

How I can do it?

Thanks.

--
Luigi

[Dimitry Sibiryakov]

Luigi Siciliano wrote 21.03.2025 17:49:
> I need in a stored procedure of firebird v. 2.5.9 to compare a date and if needs construct a date like Delphi EncodeDate(dd, mm, yyyy).

Don't fall into "XYZ problem" trap, express what you really need, not the way
you want to get it.

>   I have a DateField with a  '23.05.2025' value i need to change it in
> '01.01.2025'
>   I have a DateField with a  '03.11.2025' value i need to change it in
> '01.07.2025'

Looks like you need the first day of half-year. Modern versions of Firebird
provide function FIRST_DAY() but with ancient version 2.5 you are limited to
something like this:

dateadd(extract(month from DateField) / 2 month to
(DateField - extract(yearday from DateField))

--
WBR, SD.

[Luigi Siciliano]

Hello,

Il 21/03/2025 18:12, 'Dimitry Sibiryakov' via firebird-support ha scritto:
> Don't fall into "XYZ problem" trap, express what you really need, not
> the way you want to get it.

There is a way to test if year of Datafield is leapYear?

Thanks

--
Luigi

[Tomasz Tyrakowski]

If you want to stick with the current design of your procedure, change
the logic to something like this:

if Month > 7 then
Month = 7;
else
Month = 1;
datefield = cast(Year || '-' || Month || '-1' as date);

Crude and simple, but should work.

regards
Tomasz

[Dimitry Sibiryakov]

Luigi Siciliano wrote 21.03.2025 19:04:
>
> There is a way to test if year of Datafield is leapYear?

Your task doesn't need this but yes, you always can check if year is
divisible by 4, 100 and 400.

--
WBR, SD.

[livius...@poczta.onet.pl]

Or in the tricky way like below 😉

SELECT 

IIF(EXTRACT(DAY FROM DATEADD(-1 DAY TO CAST(EXTRACT(YEAR FROM CURRENT_DATE) || '-3-1' AS DATE)))=28, 'NO', 'YES') 

FROM 

RDB$DATABASE

Also beginning of the half-year can be done like:

SELECT 

CAST(EXTRACT(YEAR FROM CURRENT_DATE) || '-' || IIF(EXTRACT(MONTH FROM CURRENT_DATE)<7, '1', '7') || '-1' AS DATE) 

FROM RDB$DATABASE

PS> instead of CURRENT_DATE use your Datafield

---

From: 'Dimitry Sibiryakov' via firebird-support
Sent: Friday, March 21, 2025 13:04
To: firebird...@googlegroups.com
Subject: Re: [firebird-support] firebird how i construct a date from day, month ad year

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/4101c9af-f720-481a-b3c2-0dbdb39b0263%40ibphoenix.com.