May 29, 2008 at 6:00 am
Hi. I have a query that works. I was asked to modify so that the query only returns records where the expiry date is 5 days from current date.
Here is what I have:
SELECT CONVERT(VARCHAR(12), QExpiry.FieldName) AS Expiry
Returns record(s) where expiry date = Jun 3 2008 / Jun 20 2008/ Jul 19 2008
On the WHERE clause I added:
WHERE QExpiry.FieldName <= GETDATE()+5
Returned record = Jun 3 2008
Question:
Is there a better way of doing the above or was my approach correct?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
May 29, 2008 at 6:15 am
you can use the dateadd() function
May 29, 2008 at 6:17 am
Thanks. I now have a related question regards the report side that I will post to the relevent forum.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
May 30, 2008 at 9:35 am
A possible issue - maybe minor, maybe not depending on your user needs:
Getdate() returns a date time stamp including hh:mm:ss.000.
This is not an issue if your expiry column is a simple date. However, I notice that your first query truncates the time stamp with "convert". To be on the safe side you may want to truncate expiry with:
where cast(convert(varchar, expiry, 101) as smalldatetime) <= dateadd(dd, 5, getdate())
Otherwise, if there are hh:mm:ss.000 values in the expiry column, and there are multiple entries on a single day, your result set for a given day could change based on the time of day that your query runs.
OK, I worry too much...
George
May 30, 2008 at 10:23 am
gwade (5/30/2008)
A possible issue - maybe minor, maybe not depending on your user needs:Getdate() returns a date time stamp including hh:mm:ss.000.
This is not an issue if your expiry column is a simple date. However, I notice that your first query truncates the time stamp with "convert". To be on the safe side you may want to truncate expiry with:
where cast(convert(varchar, expiry, 101) as smalldatetime) <= dateadd(dd, 5, getdate())
Otherwise, if there are hh:mm:ss.000 values in the expiry column, and there are multiple entries on a single day, your result set for a given day could change based on the time of day that your query runs.
OK, I worry too much...
George
This will force an index scan instead of being able to use an index seek (if an index exists on expiry). A better method that will allow an index seek is:
where expiry <= dateadd(day, 5, dateadd(day, datediff(day, 0, getdate()), 0))
Or: where expiry <= dateadd(day, datediff(day, 0, getdate() + 5), 0)
Jeff
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 30, 2008 at 11:39 am
Excellent point about the index scan, Jeff.
I'm wondering with the new expresssion, if the date that the query is run is 2008-05-30 13:25:23.723 and the expiry value is 2008-06-04 13:19:05.880, does it matter that the value is not <= dateadd(day, 5, dateadd(day, datediff(day, 0, getdate()), 0)) ?
Does that mean we now need to increment the days added to 6, and make the comparison operator simply less than (or alternately, change the datediff numeric argument from 0 to -1). In user terms, do they want to consider an expiry value of 2008-06-04 13:19:05.880 as expiring on 2008-06-04 or 2008-06-05? If the answer is 04, then I think we need to add an extra day.
It seemed like a simple enough question to start with... 🙂
George
May 30, 2008 at 12:48 pm
Thanks for your feedback guys.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
May 30, 2008 at 2:44 pm
gwade (5/30/2008)
Excellent point about the index scan, Jeff.I'm wondering with the new expresssion, if the date that the query is run is 2008-05-30 13:25:23.723 and the expiry value is 2008-06-04 13:19:05.880, does it matter that the value is not <= dateadd(day, 5, dateadd(day, datediff(day, 0, getdate()), 0)) ?
Does that mean we now need to increment the days added to 6, and make the comparison operator simply less than (or alternately, change the datediff numeric argument from 0 to -1). In user terms, do they want to consider an expiry value of 2008-06-04 13:19:05.880 as expiring on 2008-06-04 or 2008-06-05? If the answer is 04, then I think we need to add an extra day.
It seemed like a simple enough question to start with... 🙂
George
Yes, we would probably want to use just a <, but whether or not we need to add a day all depends upon what the parameters need to be.
If the end date is inclusive - then we need to add one. If not, then we don't need to do anything at all. There are a couple of ways to approach this - all depends upon how this is called. If this is put into a stored procedure, I would have this setup as an inclusive parameter and do something like:
CREATE PROCEDURE dbo.MyProc
@end_date datetime = NULL
AS
SET @end_date = dateadd(day, 1, coalesce(@end_date, getdate()));
SET @end_date = dateadd(day, datediff(day, 0, @end_date), 0);
SELECT columns
FROM table
WHERE expiry < @end_date;
GO
And it would be called as: Execute dbo.MyProc '2008-06-04';
Or, we could pass in the number of days in the future:
CREATE PROCEDURE dbo.MyProc
@futureDays int = 5
AS
SET @end_date = dateadd(day, @futureDays + 1, getdate());
SET @end_date = dateadd(day, datediff(day, 0, @end_date), 0);
SELECT columns
FROM table
WHERE expiry < @end_date;
GO
And this would be called as: Execute dbo.MyProc 5;
Jeff
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply