SQL 从 Year 和 Quarter 创建一个 DateTime 值

2021-09-10 00:00:00 sql tsql sql-server

我知道与日程相关的里程碑的年份和季度(例如2010"和4")，我想从中选择/创建日期时间.有许多漂亮的方法可以用特定日期的格式(qq")来识别季度，但不能反过来(或者有吗?).这是使用 t-sql/SQL Server.

I know the year and the quarter (e.g. "2010" and "4") for a schedule-related milestone and I want to select/create a datetime from it. There are a number of nifty ways to identify the quarter with formats ("qq") of a particular date, but not to go the other way around (or are there?). This is with t-sql / SQL Server.

注意:日期时间应为该季度的最后天.

Note: the datetime should be for the last day of that quarter.

更新:这是我最终使用 gbn 提供的解决方案，带有 AaronLS 的变量名称，然后根据 Frank Kalis 的建议进行了缩短和改进:-) 对所有 4 个季度进行测试非常重要确保这一年得到妥善处理.感谢所有回答的人！

DECLARE @TheQuarter INT
DECLARE @theYear INT
-- Note: qq = q = quarter for the datepart
SET @TheQuarter = 1
SET @TheYear = 2011
SELECT DATEADD(YEAR, @TheYear-1900, DATEADD(qq, @TheQuarter, -1))
-- 2011-03-31 00:00:00.000

SET @TheQuarter = 2
SET @TheYear = 2011
SELECT DATEADD(YEAR, @TheYear-1900, DATEADD(qq, @TheQuarter, -1))
-- 2011-06-30 00:00:00.000

SET @TheQuarter = 3
SET @TheYear = 2011
SELECT DATEADD(YEAR, @TheYear-1900, DATEADD(qq, @TheQuarter, -1))
-- 2011-09-30 00:00:00.000

SET @TheQuarter = 4
SET @TheYear = 2011
SELECT DATEADD(YEAR, @TheYear-1900, DATEADD(qq, @TheQuarter, -1))
-- 2011-12-31 00:00:00.000

以下是一些从日期中获取季度但不是相反的 q:计算当前季度的最后一天；计算季度的最后一天；在 SQL Server 中存储季度和年度的最佳方式?

Here are a few q's that fetch the quarter from the date but not the other way around: Calculate the Last Day in the CURRENT quarter; Calculate the last day of the quarter; Best way to store quarter and year in SQL Server?

推荐答案

永远不要使用字符串进行日期时间转换:格式、语言等错误太多了.

Never use strings for datetime conversions: too much to go wrong with formats, language etc.

保持在日期时间类型...

Keep it in the datetime type...

Select dateadd(day, -1, 
                       dateadd(year, @year-1900,
                                          dateadd(quarter, @qq, 0)
                                     )
             )

相关文章