How many days from first day of the current year till today

Question

I'm trying to get day count of from the first day of the current year till today

For example:
Today = 24/07/2023
FirstDate = 01/01/2023
LastDate = 24/07/2023
The result is 204 days

Is it possible to get the result with just a query (no stored procedure)?

Answer 1

You can use the built-in function DATEDIFF for this:

select datediff(day, date '2023-01-01', date '2023-07-24') from rdb$database

Result: 204

dbfiddle: https://dbfiddle.uk/wbKAN8Mt

If you want to dynamically find out the first day of the year from the date, you can use the FIRST_DAY function, but this requires Firebird 4.0:

select date_val, datediff(day, first_day(of year from date_val), date_val)
from (
  select date '2023-07-24' as date_val from rdb$database
  union all select date '2021-01-02' from rdb$database
  union all select date '2022-12-31' from rdb$database
) a

Result:

DATE_VAL	DATEDIFF
2023-07-04	204
2021-01-02	1
2022-12-31	364

dbfiddle: https://dbfiddle.uk/gl9n-m0p

As pointed out in the comments by user13964273, if you want to know the days since the start of the year, you can also use EXTRACT(YEARDAY FROM <value>), because 0 is 1st of January, etc, it will produce the same result as datediff(day, first_day(of year from date_val), date_val):

Modified example:

select date_val, extract(yearday from date_val)
from (
  select date '2023-07-24' as date_val from rdb$database
  union all select date '2021-01-02' from rdb$database
  union all select date '2022-12-31' from rdb$database
) a

dbfiddle: https://dbfiddle.uk/NVimzbAz