英文:
How many days from first day of the current year till today
问题
我试图从今年的第一天到今天获取天数
例如：
今天 = 24/07/2023
第一天 = 01/01/2023
最后一天 = 24/07/2023
结果是204天
是否可以仅使用查询（无存储过程）获得结果？
英文:
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)?
答案1
得分: 1
你可以使用内置函数DATEDIFF来实现此功能：
select datediff(day, date '2023-01-01', date '2023-07-24') from rdb$database
结果：204
dbfiddle链接：https://dbfiddle.uk/wbKAN8Mt
如果你想动态查找一年中的第一天，你可以使用FIRST_DAY函数，但这需要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
结果：
| DATE_VAL | DATEDIFF |
|---|---|
| 2023-07-04 | 204 |
| 2021-01-02 | 1 |
| 2022-12-31 | 364 |
dbfiddle链接：https://dbfiddle.uk/gl9n-m0p
正如user13964273在评论中指出的，如果你想知道自年初以来的天数，你也可以使用EXTRACT(YEARDAY FROM <value>)，因为0表示1月1日，等等，它将产生与datediff(day, first_day(of year from date_val), date_val)相同的结果：
修改后的示例：
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
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论