CODEWITHSUNDEEP
sqlsql-server-2008
Akshay
Akshay

Reputation: 1472

How can I calculate the required date in SQL Server 2008?

Need to check if today's date < 20 then set result var as 1st of next month. If today's date > 20 then set result var as 1st of next month following the next month ?

Example:

Upvotes: 2

Views: 63

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

This will give you the result:

DECLARE @d DATE = '20150924'
SELECT CASE WHEN DAY(@d) < 20 
            THEN DATEADD(mm, 1, DATEADD(dd, -DAY(@d) + 1, @d)) 
            ELSE DATEADD(mm, 2, DATEADD(dd, -DAY(@d) + 1, @d)) END;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This seems a bit tricky because of the date arithmetic:

set @var = (case when day(getdate()) < 20
                 then cast(dateadd(month, 1, dateadd(day, 1 - day(getdate()), getdate())) as date)
                 else cast(dateadd(month, 2, dateadd(day, 1 - day(getdate()), getdate())) as date)
            end);

Here is a SQL Fiddle that shows the calculation.

Upvotes: 2

Related Questions