SQL Server : date automate TSQL Query

Question

I want SQL query to get the dates automatically,

Please Note: year should always start on the Last Sunday of January.

I want a SQL query for below example; assume current date is '2017-01-01'

SQL should pick these dates:

Between '2016-01-31' and '2016-12-31'

(between 'Start of the Year (Last Sunday of January Month)' and 'Last day (Saturday) of previous week')

I have this query:

Between 
    case 
       When DatePart(w, DateAdd(d, 30, DATEADD(y, - DatePart(y, GetDate()) + 1, GetDate()))) = 1
          Then DateAdd(d, 30, DATEADD(y, - DatePart(y, GetDate()) + 1, convert(date, GetDate())))
          Else DateAdd(d, 7 - DatePart(w, DateAdd(d, 30, DATEADD(y, - DatePart(y, GetDate()) + 1, GetDate()))) + 1, DateAdd(d, 30, DATEADD(y, - DatePart(y, GetDate()) + 1, convert(date, GetDate())))
                )
       end
and 
    convert(date, dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 5)

The above query is returning wrong results:

Example 1: Wrong

Assume current date is '2017-01-01', the SQL query returns these dates:

Between '2017-02-05' and '2016-12-31'

which are wrong.

Example 2: Correct

Assume current date is '2017-02-12', then the SQL query returns these dates:

Between '2017-02-05' and '2017-02-11'

which are the correct dates - OK.

The problem is always when the current date is in January

How to fix example 1 please? Any updated SQL query?

Which version of SQL Server are you running? If at least 2012, the EOMONTH function will probably be of use to you! — Bridge
You mentioned year should always start on the 1st February, then how you will get 'Between '2016-01-31' and '2016-12-31'' for '2017-01-01', i think it should be 'Between '2016-02-07' and '2016-12-31' — Abdul Rasheed

Danie Schoeman Danie Schoeman · Accepted Answer · 2017-01-03T11:37:17

If Feb of any year is your fixed parameter then you compare the current date to it and based on the result use it or not:

    DECLARE @GetMyDate datetime = '2017-01-01'--GETDATE()

    SELECT  @GetMyDate,
            CASE 
                WHEN    @GetMyDate < DATEADD(Day,-1,CONVERT(datetime, CONVERT(varchar(4), (year(@GetMyDate))) + '-02-01'))
                THEN    DATEADD(Day,-1,CONVERT(datetime, CONVERT(varchar(4), (year(@GetMyDate)-1)) + '-02-01'))
                ELSE    DateAdd(d, 7 - DatePart(w, DateAdd(d, 30, DATEADD(y, - DatePart(y, @GetMyDate) + 1, @GetMyDate))) + 1, DateAdd(d, 30, DATEADD(y, - DatePart(y, @GetMyDate) + 1, convert(date, @GetMyDate))))
            END AS StartDate,
            convert(date, dateadd(wk, datediff(wk, 0, @GetMyDate) - 1, 0) + 5)  AS EndDate

Update:

BETWEEN CASE 
            WHEN    GETDATE() <= DATEADD(Day,-0,CONVERT(datetime, CONVERT(varchar(4), (year(GETDATE()))) + '-02-01'))
            THEN    DATEADD(Day,-0,CONVERT(datetime, CONVERT(varchar(4), (year(GETDATE())-1)) + '-02-01'))
            ELSE    DateAdd(d, 7 - DatePart(w, DateAdd(d, 30, DATEADD(y, - DatePart(y, GETDATE()) + 1, GETDATE()))) + 1, DateAdd(d, 30, DATEADD(y, - DatePart(y, GETDATE()) + 1, convert(date, GETDATE()))))
        END 
        AND
        CASE 
            WHEN    GETDATE() = DATEADD(Day,-0,CONVERT(datetime, CONVERT(varchar(4), (year(GETDATE()))) + '-02-01'))
            THEN    DATEADD(Day,-1,CONVERT(datetime, CONVERT(varchar(4), (year(GETDATE()))) + '-02-01'))
            ELSE    Convert(date, dateadd(wk, datediff(wk, 0, GETDATE()) - 1, 0) + 5)
        END

SQL Server : date automate TSQL Query

1 Answers