Date Query

Question

Date Query

sc-w

Hall of Fame

Points: 3667
More actions
January 4, 2010 at 4:11 am

#217935

Hi,
I want to write a query from a single table that shows results based on the date the query is run and the previous two months only. The todays date need s to be dynamic and can't be hard coded which is where i need a little help.
I have seen date functions but can't see how to apply them in the query to say today - two months.
Any advice would be a great start to the New Year. Thanks
select * from wce_history where recordedtime >= "two months ago from date query is run"

Viewing 10 posts - 1 through 9 (of 9 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

select * from wce_history where recordedtime >= dateadd(mm,-2,getdate())

dateadd is used to calculate date offsets based on a given date. Getdate() is current date, current time. Please see BOL (Books Online, the SQL Server help system installed together with SQL Server) for details.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sc-w Hall of Fame Points: 3667 More actions · Answer 2

Thank you for that, just what i needed. I will read up on it but i can see the logic! Thanks again.

PhilPacha Hall of Fame Points: 3645 More actions · Answer 3

There's a problem with lmu's solution if your RecordedTime column contains a time component. If you run his logic at 3:00 PM on Jan 1, it will pick up everything from Nov 1, 3 PM and later. If you actually want the time portion to be all-day, change the GETDATE() portion of his logic to this: DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)

This piece of logic determines the number of DAYS between the current date-time and Jan 1, 1900 00:00:00.000 AM, then adds that day-count to Jan 1, 1900. This results in a date with a time-value of midnight.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

PhilPacha (1/4/2010)
There's a problem with lmu's solution if your RecordedTime column contains a time component. If you run his logic at 3:00 PM on Jan 1, it will pick up everything from Nov 1, 3 PM and later. If you actually want the time portion to be all-day, change the GETDATE() portion of his logic to this: DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)
This piece of logic determines the number of DAYS between the current date-time and Jan 1, 1900 00:00:00.000 AM, then adds that day-count to Jan 1, 1900. This results in a date with a time-value of midnight.

Agreed.

LutzM SSC Guru Points: 107049 More actions · Answer 5

lmu92 (1/4/2010)
select * from wce_history where recordedtime >= dateadd(mm,-2,getdate())
dateadd is used to calculate date offsets based on a given date. Getdate() is current date, current time. Please see BOL (Books Online, the SQL Server help system installed together with SQL Server) for details.

I thought my "disclaimer" (bold marked) would have been sufficient enough to mark that it will return all values 2 month back from NOW.

PhilPacha (1/4/2010)
If you actually want the time portion to be all-day, change the GETDATE() portion of his logic to this: DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)

Using your example: How do you know that the data have to start with the Nov 1 and not with Nov 2nd? Including complete Nov 1st will have more than two month of data (+15hrs), starting with Nov 2nd will have less (-9hrs)... There was nothing specified what to do with the first day (since usually not covering 24hrs...)

I, personally, don't think my solutions has a "problem"... I used every information I had.

The "problem" is, that we don't know the exact definition of "2 month".

And I failed to point at it more obvious...

Edit:

Example:

The code is used in a report running at 6am each day. The company is running 2 shifts, each 10hrs, starting at 6am. PhilPachas solution would include the last 2hrs from the second shift from Nov 1st. I'd expect this to be wrong...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Charles Kincaid SSChampion Points: 13593 More actions · Answer 6

I would use this form:SELECT SomeThing

FROM wce_history

WHERE recordedtime >= DATEADD(MONTH, -2, DATEDIFF(DAY, 0, GETDATE()))

As others have pointed out there are issues with the time portion of datetime. The DATEDIFF(DAY, 0, GETDATE()) gets you to the beginning of the current day on the SQL server. Then the DATEADD thing backs that up by the interval chosen. How that interval works is shown in Books On Line. You use what you get. If you need something different then you will have to generate a more specific date yourself.

Then there is the Time Zone issue. Take a server on the East coast and a user on the West coast. We have avoided that by storing all our datetime values in UTC. They are converted to and from local by the application. But there is a UTC replacement for GETDATE().

ATBCharles Kincaid

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

lmu92 (1/4/2010)
lmu92 (1/4/2010)
select * from wce_history where recordedtime >= dateadd(mm,-2,getdate())
dateadd is used to calculate date offsets based on a given date. Getdate() is current date, current time. Please see BOL (Books Online, the SQL Server help system installed together with SQL Server) for details.
I thought my "disclaimer" (bold marked) would have been sufficient enough to mark that it will return all values 2 month back from NOW.
PhilPacha (1/4/2010)
If you actually want the time portion to be all-day, change the GETDATE() portion of his logic to this: DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)
Using your example: How do you know that the data have to start with the Nov 1 and not with Nov 2nd? Including complete Nov 1st will have more than two month of data (+15hrs), starting with Nov 2nd will have less (-9hrs)... There was nothing specified what to do with the first day (since usually not covering 24hrs...)
I, personally, don't think my solutions has a "problem"... I used every information I had.
The "problem" is, that we don't know the exact definition of "2 month".
And I failed to point at it more obvious...
Edit:
Example:
The code is used in a report running at 6am each day. The company is running 2 shifts, each 10hrs, starting at 6am. PhilPachas solution would include the last 2hrs from the second shift from Nov 1st. I'd expect this to be wrong...

And Phil had a disclaimer in his post as well:

If you actually want the time portion to be all-day, change the GETDATE() portion of his logic to this: DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)

LutzM SSC Guru Points: 107049 More actions · Answer 8

I didn't question Phils solution as "having a problem"...

I might have misinterpreted / overreacted though.

Probably caused by some bad private news I received shortly before... Shouldn't have posted in that mood in the first place. Sorry!

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Lutz, Been there done that with the same regrets. Just thought I'd point out his disclaimer as i did agree with his post as well.

What would really have helped would have been clearer specs from the OP regarding what was meant by previous 2 months of data. That and some sample data to demonstrate the requirements would have been great.

Hope things are going better or will be soon.

I know I am hoping for that here myself.