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?


Show source
| sql-server   | tsql   | sql   | date   | datetime   2017-01-03 12:01 1 Answers

Answers ( 1 )

  1. 2017-01-03 12:01

    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
    
◀ Go back