CODEWITHSUNDEEP
sqlsql-server
user4493428
user4493428

Reputation: 1

SQL: How to get the following day from end of the month

Example:

If I run this query today (26 January), my result will be addedon +1 day which is 27 January.

select addedon 
from member
Where Day(addedon) = Day(DateAdd(dd, 1, GetDate()))
and Month(addedon) = Month(DateAdd(dd, 1, GetDate()))

However, when I run this query on 31 January, it is giving me 1 January result instead of 1 Feb.

Please help! Note that I can only use simple select statement (like the above) in my application.

Upvotes: 0

Views: 49

Answers (2)

John Castleman
John Castleman

Reputation: 1561

I'm going to guess that your datetime are - as is pretty typical - stored in your database as UTC datetime, and that you are somewhere in the Western Hemisphere, and that calling Month on a UTC time after, say, 9:30EDT (or 8:30CDT) is returning 2, matching Month(DateAdd(dd, 1, GetDate())).

Most of the time, you want to store your datetime in your database as UTC, which means - most of the time - you want to write your queries the same way (i.e., using GetUtcDate).

As a test, rewrite you query using GetUtcDate in place of GetDate, and see if your results change.

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

To get the beginning of the next month: 

SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0)

This will return member whose addedon falls on the first day of the next month.

SELECT addedon
FROM member
WHERE
    addedon >= DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0)
    AND addedon < DATEADD(DD, 1, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0))

This will return member whose addedonfalls on the next day:

SELECT addedon
FROM #member
WHERE
    addedon >= DATEADD(DD, DATEDIFF(DD, 0, GETDATE()) + 1, 0)
    AND addedon < DATEADD(DD, DATEDIFF(DD, 0, GETDATE()) + 2, 0)

Upvotes: 1

Related Questions