First day of the next month

Question

I'm trying get the results where it only displays OrderDates before the LAST day of the CURRENT month. I'm guessing it would be like this...

SELECT OrderDate
FROM Orders
WHERE OrderDate < (code for first day of the next month?)

Beckman

please search SO before posting a question: asked many times previously – Marlenmarlena

So you can have orders in the future? Just want to check that you do intend to support post-dating these and that you're not making the problem harder than it needs to be when you could just look at current_timestamp. – Antoinetteanton

Answer 1

First day of next month:

sql-server 2012+

DATEADD(d, 1, EOMONTH(current_timestamp))

sql-server 2008 and older:

DATEADD(m, DATEDIFF(m, -1, current_timestamp), 0)

Riposte

Answer 2

Your question is somewhat ambiguous, but this will give you '(code for the first day of the month)'

SELECT OrderDate
FROM Orders 
WHERE ORDERDATE < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

Peshitta

I think he wants first day of next month, but for that all you need to do is add a +1 after the closing paren for the DATEDIFF() call, so close enough. – Antoinetteanton

Answer 3

SELECT DATEADD(month, DATEDIFF(month, 0, getdate())+1, 0) AS StartOfMonth

Dali

Answer 4

Let's understand the method to get first day of current month, we can fetch day component from the date (e.g. @mydate) using DATEPART and subtract this day component to get last day of previous month. Add one day to get first day of current month. E.g. @mydate = 10/8/2019, subtract day component (8 days) will give us 9/30/2019. Now add one day in this outcome to get first of the current month - 10/1/2019.

Formula - DATEADD(day,1,DATEADD(day, -DATEPART(day,@mydate), @mydate))

Now First of the next month - Add one month in above formula DATEADD(month,1,(DATEADD(day,1,DATEADD(day, -DATEPART(day,@mydate), @mydate))))

Kopeisk

Answer 5

Try this

SELECT OrderDate
FROM Orders 
WHERE ORDERDATE < DATEADD(dd,-(DAY(DATEADD(mm,1,getdate()))-1),DATEADD(mm,1,getdate()))

Take a look at here

Mousetail

Answer 6

SELECT OrderDate FROM Orders WHERE orderdate < (LAST_DAY(CURRENT DATE) + 1)

Eumenides

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. – Hooghly

While this code may answer the question, it is better to explain how to solve the problem and provide the code as an example or reference. Code-only answers can be confusing and lack context. – Begird

Answer 7

    Select Convert(date,Dateadd(dd,1 - DATEPART(dd,getdate()), DATEADD(mm,1,getdate())),103)

Antibiotic

While this code may/may not solve the question, including an explanation of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please edit your answer to add explanations. – Sawyor

Answer 8

For sql-server 2012 ->

Using DateFromParts will do the trick. +1 to get the next month

SELECT OrderDate
FROM Orders
WHERE OrderDate < DATEFROMPARTS(YEAR(Getdate()),MONTH(Getdate())+1,1)

https://learn.microsoft.com/en-us/sql/t-sql/functions/datefromparts-transact-sql

Grogram