我如何在规定的时间内每年返回日期 [英] How do I return dates within a stated period yearly
本文介绍了我如何在规定的时间内每年返回日期的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在构建一个SQL报告,需要从明年的31/03 / 01年开始每年运行。
如何在SQL
我尝试过:
I am building a SQL report that needs to run yearly from the 31/03/ to 01/04 the next year.
How can I code this in SQL
What I have tried:
sales_date BETWEEN DateAdd(yy,-1,(DateAdd(dd, -1, '01/04/YYYY'))) AND '01/04/YYYY'
推荐答案
与 Peter's Leow 相反[ ^ ]解决方案,我建议使用:
- 适用于MS SQL Server 2012及更高版本: DATEFROMPARTS(Transact-SQL ) [ ^ ]功能
- 对于早期版本 - DATEADD(Transact-SQL) [ ^ ], GETDATE() [ ^ ], DATEDIFF() [
例如:
今天:
In opposite to Peter's Leow[^] solution, i'd suggest to use:
- for MS SQL Server 2012 and higher: DATEFROMPARTS (Transact-SQL)[^] function
- for earlier versions - a combination of DATEADD (Transact-SQL)[^], GETDATE()[^], DATEDIFF()[^], etc.
For example:
today:
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
昨天:
yesterday:
DATEADD(day, DATEDIFF(day, 0, GETDATE()), -1)
蒙特的开始h:
start of month:
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
上个月末:
end of last month:
DATEADD(month, DATEDIFF(month, 0, GETDATE()), -1)
下月开始:
start of next month:
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 31)
根据您的需要进行更改。试试!
提示:
Change it to your needs. Try!
Tip:
DECLARE @months INT = 12+(4-MONTH(GETDATE())-12)
SELECT DATEADD(month, DATEDIFF(month, 0, DATEADD(month, @months, GETDATE())), -1) AS FiscalYearStart,
DATEADD(month, DATEDIFF(month, 0, DATEADD(month, @months+12, GETDATE())), 0) AS FiscalYearEnd
返回:
returns:
FiscalYearStart FiscalYearEnd
2017-03-31 00:00:00.000 2018-04-01 00:00:00.000
如果我理解正确,你试图在当年3月31日到次年4月1日之间获得记录,然后
1.为currentyear-3-31创建一个日期时间,如下所示:
If I understand you correctly, you are trying to get records between the 31 March of the current year till 1 April of the following year, then
1. Create a datetime for currentyear-3-31 like this:
SELECT CAST(CAST(DATEPART(year, getdate()) AS VARCHAR) + '-3-31' AS DATETIME)
和
2.像这样为nextyear-4-1创建一个日期时间:
; and
2. Create a datetime for nextyear-4-1 like this:
SELECT CAST(CAST(DATEPART(year, getdate())+1 AS VARCHAR) + '-4-1' AS DATETIME)
参见演示:在TSQL,Sql Server中创建特定日期 [ ^ ]
这篇关于我如何在规定的时间内每年返回日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文