from today to last one month data

Question

from today to last one month data

adisql

SSCrazy

Points: 2893
More actions
October 5, 2017 at 11:12 am

#325696

Hi,
Need to select last one month( from today to last 30 days ).
'DATEOFPURCHASE' datatype is datetime,notnull.
===================================================================================
SELECT * FROM Tablename(nolock) WHERE DATEOFPURCHASE [between today to last 30days] AND > 2011-09-18 00:00:00.000
Can i get the query please .
Thank you.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

adisql - Thursday, October 5, 2017 11:12 AM
Hi,
Need to select last one month( from today to last 30 days ).
'DATEOFPURCHASE' datatype is datetime,notnull.
===================================================================================
SELECT * FROM Tablename(nolock) WHERE DATEOFPURCHASE [between today to last 30days] AND > 2011-09-18 00:00:00.000
Can i get the query please .
Thank you.

Have you heard of DATEADD() function?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Thom A SSC Guru Points: 99133 More actions · Answer 2

Also, I feel there is no need to check that "today", and "30 days ago" is greater than 2011-09-18. Unless you invent a time machine and go back and run your query on or earlier than 2011-10-17, then it's always going to be more than that date. 🙂

The syntax is almost like saying "Select a number between 10 and 20 that is greater than 5". All numbers between 10 and 20 are greater than 5, so there's no need for the extra requisite..

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

adisql SSCrazy Points: 2893 More actions · Answer 3

sorry i mistaken 🙂

i want last onemonth data .

adisql SSCrazy Points: 2893 More actions · Answer 4

i got it .. thank you

SELECT count(*) FROM table
WHERE
DATEOFPURCHASE >= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0)
AND DATEOFPURCHASE < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)

sgmunson SSC Guru Points: 110640 More actions · Answer 5

adisql - Thursday, October 5, 2017 11:24 AM
sorry i mistaken 🙂
i want last onemonth data .

A couple of ways to interpret that. Do you want last full calendar month, or from exactly 1 month ago through right now, or perhaps through the end of the day yesterday, and are we talking midnight or end of the business day ? Those seemingly small things can make a rather large difference in what you get out of tthe query, depending on exactly when you run it. So, therefore, have you taken into account what happens after you cross a calendar boundary? That can also mess with your query methodology. Thus we need you to be a LOT more detailed about exactly what you want.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

adisql SSCrazy Points: 2893 More actions · Answer 6

Steve,

I am looking for ... from exactly 1 month ago through right now.

Thanks

sgmunson SSC Guru Points: 110640 More actions · Answer 7

adisql - Thursday, October 5, 2017 11:27 AM
i got it .. thank you
SELECT count(*) FROM table
WHERE
DATEOFPURCHASE >= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0)
AND DATEOFPURCHASE < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)

Understand that not all months are 31 days long. Next month is only 30 days. Will you then have to change this report every month because of the number of days in it?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

whenriksen SSCarpal Tunnel Points: 4752 More actions · Answer 8

1 month != 30 days != 4 weeks. Make sure you understand the impact the different intervals can have on your data. Also be aware of the how the between clause works for data ranges.

Wes
(A solid design is always preferable to a creative workaround)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

sgmunson - Thursday, October 5, 2017 11:33 AM
adisql - Thursday, October 5, 2017 11:27 AM
i got it .. thank you
SELECT count(*) FROM table
WHERE
DATEOFPURCHASE >= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0)
AND DATEOFPURCHASE < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)
Understand that not all months are 31 days long. Next month is only 30 days. Will you then have to change this report every month because of the number of days in it?

Actually, that's the months from 1900-02-01 added to 1900-01-01. And January from 1900 has always had 31 days. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

adisql SSCrazy Points: 2893 More actions · Answer 10

sgmunson - Thursday, October 5, 2017 11:33 AM
adisql - Thursday, October 5, 2017 11:27 AM
i got it .. thank you
SELECT count(*) FROM table
WHERE
DATEOFPURCHASE >= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0)
AND DATEOFPURCHASE < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)
Understand that not all months are 31 days long. Next month is only 30 days. Will you then have to change this report every month because of the number of days in it?

Steve,

I am looking for ... from exactly 1 month ago through right now.
can you suggest the code .

Thanks

Luis Cazares SSC Guru Points: 183706 More actions · Answer 11

adisql - Thursday, October 5, 2017 11:56 AM
Steve,
I am looking for ... from exactly 1 month ago through right now.
can you suggest the code .
Thanks

Why are you over complicating this? If you just want exactly 1 month ago, just add -1 months to the current date.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

adisql SSCrazy Points: 2893 More actions · Answer 12

Luis Cazares - Thursday, October 5, 2017 12:00 PM
adisql - Thursday, October 5, 2017 11:56 AM
Steve,
I am looking for ... from exactly 1 month ago through right now.
can you suggest the code .
Thanks
Why are you over complicating this? If you just want exactly 1 month ago, just add -1 months to the current date.

Thank you .

sgmunson SSC Guru Points: 110640 More actions · Answer 13

Luis Cazares - Thursday, October 5, 2017 11:43 AM
sgmunson - Thursday, October 5, 2017 11:33 AM
adisql - Thursday, October 5, 2017 11:27 AM
i got it .. thank you
SELECT count(*) FROM table
WHERE
DATEOFPURCHASE >= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0)
AND DATEOFPURCHASE < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)
Understand that not all months are 31 days long. Next month is only 30 days. Will you then have to change this report every month because of the number of days in it?
Actually, that's the months from 1900-02-01 added to 1900-01-01. And January from 1900 has always had 31 days. 😉

Okay.. goofball way to go about it, and honestly, I didn't pick up that it was actually Feb 1 until I stopped and really thought it through, and sure enough, good old 0-based math got me.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)