Sql-server – Customized date range in sql

sql serversql-server-2008sql-server-2008-r2

We have got a customized quarter list from client which we need to take reference to get data in SQL. Could you please help.

30-Oct  29-Jan AS Quarter1
30-Jan  30-Apr AS Quarter2
01-May  30-Jul AS Quarter3
31-Jul  29-Oct AS Quarter4

If today falls between any of the quarter we need start date in date format
for example if today is Jan/02/2018 then it shall return start date as 30-10-2017 00:00:00 and end date as 29-01-2018 00:00:00
if today is Dec/30/2017 then it shall return start date as 30-10-2017 00:00:00 and end date as 29-01-2018 00:00:00

Best Answer

Assumption:

  • some batch work (either as part of a SQL batch, or perhaps a user-defined function) is acceptable (otherwise the batch code could be rolled into a couple additional CTEs)

A batch/CTE solution:

declare @mydate  datetime,

        @mymon   int,
        @myday   int,
        @myyr    int,

        @startyr char(4),
        @endyr   char(4)

select  @mydate = '01/02/2018'

-- break @mydate into numeric components to make it a little easier in the
-- next step to determine our start/end years

select  @mymon = month(@mydate), 
        @myday = day(@mydate),
        @myyr  = year(@mydate)

-- determine our start/end years based on current year of @mydate; since we'll
-- be feeding these variables into a convert(datetime,<string>) construct, go
-- ahead and convert to char(4) here in order to minimize coding later

select  @startyr = convert(char(4), case when @mymon = 10 and @myday >= 30 then @myyr
                                         when @mymon in (11,12)            then @myyr
                                                                           else @myyr - 1
                                    end),
        @endyr   = convert(char(4), case when @mymon = 10 and @myday >= 30 then @myyr + 1
                                         when @mymon in (11,12)            then @myyr + 1
                                                                           else @myyr
                                    end);

-- use a CTE to build our list of quarters, using the @starty/@endyr variables
-- to create the datetime ranges that make up the quarters in which 
-- @mydate date can fall within

with
quarters as

(select *
 from   (values ('Quarter1', convert(datetime,'10/30/'+@startyr), convert(datetime,'01/29/'+@endyr) ),
                ('Quarter2', convert(datetime,'01/30/'+@endyr),   convert(datetime,'04/30/'+@endyr) ),
                ('Quarter3', convert(datetime,'05/01/'+@endyr),   convert(datetime,'07/30/'+@endyr) ),
                ('Quarter4', convert(datetime,'07/31/'+@endyr),   convert(datetime,'10/29/'+@endyr) ) ) as q(qtr, qstart, qend)
)

-- now it's just a matter of finding which datetime range (aka quarter)
-- @mydate falls between

select q.qtr, q.qstart, q.qend

from   quarters q
where  @mydate between q.qstart and q.qend;

Some sample runs (changing the value of @mydate):

-- @mydate = '10/29/2017'

 qtr      | qstart              | qend               
 -------- | ------------------- | -------------------
 Quarter4 | 07/31/2017 00:00:00 | 29/10/2017 00:00:00

-- @mydate = '10/30/2017'

 qtr      | qstart              | qend               
 -------- | ------------------- | -------------------
 Quarter1 | 30/10/2017 00:00:00 | 29/01/2018 00:00:00

-- @mydate = '08/17/2016'

 qtr      | qstart              | qend               
 -------- | ------------------- | -------------------
 Quarter4 | 31/07/2016 00:00:00 | 29/10/2016 00:00:00

-- @mydate = '01/23/2016'

 qtr      | qstart              | qend               
 -------- | ------------------- | -------------------
 Quarter1 | 30/10/2015 00:00:00 | 29/01/2016 00:00:00

Here's a dbfiddle

Related Question