32

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?)
Paul Rowland
  • 8,244
  • 12
  • 55
  • 76
jaramore
  • 389
  • 1
  • 3
  • 12
  • 4
    please search SO before posting a question: asked many times previously – Mitch Wheat Mar 25 '14 at 01:49
  • 1
    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. – Joel Coehoorn Mar 25 '14 at 02:39

8 Answers8

74

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)
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
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)
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
E.J. Brennan
  • 45,870
  • 7
  • 88
  • 116
  • 1
    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. – Joel Coehoorn Mar 25 '14 at 02:41
2
SELECT DATEADD(month, DATEDIFF(month, 0, getdate())+1, 0) AS StartOfMonth
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
viju
  • 21
  • 2
2

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

Ramesh Chaudhary
  • 21
  • 3
1

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

Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
0
SELECT OrderDate FROM Orders WHERE orderdate < (LAST_DAY(CURRENT DATE) + 1)
Jayendran
  • 9,638
  • 8
  • 60
  • 103
Bobok
  • 9
  • 1
  • 1
    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. – Alex Riabov Aug 21 '18 at 15:17
  • 1
    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. – Robert Columbia Aug 21 '18 at 22:24
0
    Select Convert(date,Dateadd(dd,1 - DATEPART(dd,getdate()), DATEADD(mm,1,getdate())),103)
Prem
  • 119
  • 1
  • 4
  • While this code may/may not solve the question, [including an explanation](https://meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) 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. – Drew Aug 16 '19 at 06:26
0

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

Bunkerbuster
  • 963
  • 9
  • 17