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?)

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)

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)

Answer 3

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

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))))

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

Answer 6

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

Answer 7

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

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://docs.microsoft.com/en-us/sql/t-sql/functions/datefromparts-transact-sql