获取季度的开始日期和结束日期 [英] Get Quarters StartDate and EndDate from Year

问题描述

我使用的是 SQL Server 2008.我想获取一年中所有季度的开始日期和结束日期.例如，如果我在查询中传递 2013 则输出应该像

I am using SQL Server 2008. I want to get Start Date and End Date of all quarters of year. For example if I pass 2013 in query then output should like

StartDate                 EndDate                   QuarterNo
-------------------------------------------------------------
2013-04-01 00:00:00.000   2013-06-30 00:00:00.000      1
2013-07-01 00:00:00.000   2013-09-30 00:00:00.000      2
2013-10-01 00:00:00.000   2013-12-31 00:00:00.000      3
2014-01-01 00:00:00.000   2014-03-31 00:00:00.000      4

由于财政年度从 4 月 1 日开始，我想从 4 月 1 日开始第一季度.我怎样才能得到这个输出?感谢您的帮助...

Because of Financial Year start from 1st April, I want to get 1st quarter start from 1st April. How can I get this output? Thanks for help...

推荐答案

select 
    dateadd(M, 3*number, CONVERT(date, CONVERT(varchar(5),@year)+'-1-1')),
    dateadd(D,-1,dateadd(M, 3*number+3, CONVERT(date, CONVERT(varchar(5),@year)+'-1-1'))),
    Number QuarterNo
from master..spt_values 
where type='p' 
and number between 1 and 4  

您可能想使用日期，而不是日期时间，否则季度最后一天的那一天不会包含在您的季度中(例如:2013-06-30 14:15)

You'll probably want to use dates, not datetimes, otherwise nothing during the day of the last day of the quarter is included in your quarter (eg: 2013-06-30 14:15)

相反，使用 datepart

select ((DATEPART(q,@date)+2) % 4)+1

这篇关于获取季度的开始日期和结束日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！