problem using getdate() in "where" clause
-
Greetings.
I need to select records based on their distance from today, and because of the datetime format, I'm having trouble getting the right result.
Actually, I *can* get the right result (in this case, the records with invdate 10 days before today), using this highly awkward syntax:
---------------------------
SELECT ARJOBHD.INVNO, ARJOB.PRICE, ARJOB.FRTAMT, ARJOBHD.INVDATE
FROM ARJOB INNER JOIN
ARJOBHD ON ARJOB.INVNO = ARJOBHD.INVNO
WHERE (ARJOBHD.INVDATE > GETDATE() - 11) AND (ARJOBHD.INVDATE < GETDATE() - 10) --------------------------- But I know there's a better solution ... Can someone help me figure out what it is? Thanks in advance. Kenn
-
You could use DateDiff(), but there's not enough info to know if that gives the correct answer:
WHERE DateDiff(dd, GetDate(), ARJOBHD.INVDATE) = 10
However, how do you treat days ? For eaxmple, is an Invoice created at 1 second before midnight on Jan 10th seen as being 1 day before if you run your query 2 seconds later, at 1 second after midnight on Jan 11th ?
-
I want to totally ignore time and only look at the date. So, yes, an invoiced created 1 second before midnight on Jan 10 would be seen as 1 day before if I ran the query 1 second after midnight on Jan 11.
-
You can do a double convert on GetDate() to get the time stripped off...
SELECT ARJOBHD.INVNO, ARJOB.PRICE, ARJOB.FRTAMT, ARJOBHD.INVDATE
FROM ARJOB INNER JOIN
ARJOBHD ON ARJOB.INVNO = ARJOBHD.INVNO
WHERE ARJOBHD.INVDATE > CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 11, 101), 101)
AND ARJOBHD.INVDATE < CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 10, 101), 101)
-
I'm not sure that query will allow the use of any index that may exist on ARJOBHD.INVDATE.
Could be a big problem if there are many rows!
RON
-
CREATE FUNCTION dbo.fn_DateOnly(@date DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(day, DATEDIFF(day, 0, @date), 0)
END
and use this in your WHERE
WHERE DATEDIFF(day, dbo.fn_DateOnly(ARJOBHD.INVDATE), dbo.fn_DateOnly(GETDATE())) = 10
I hope did not mess up the syntax again...
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
that was PERFECT !!!! thanks so much for your help.
-
Yes, but you don't need that user-defined function. SQL's DateDiff works on day boundaries if days is the requested interval:
Using the example of an invoice at 1 second before midnight on Jan 10th, and the query run at 1 second after midnight on Jan 11th to be considered as 1 day ago:
Select DateDiff(dd, '10 Jan 2006 23:59:59', '11 Jan 2006 00:00:01')
That returns 1, which correctly meets the requirement, so datediff can be used on its own:
WHERE DATEDIFF( dd, ARJOBHD.INVDATE, GETDATE() ) = 10
Also, note the original SQL as posted contains a logic bug. In using an expression like (GETDATE() - 10), this does indeed subtract 10 days, but leaves the time portion, so the original query as posted is time-of-day dependant which is not the stated requirement.
Ron K is right on the mark, use of any of these prevents index usage if InvDate is indexed. If that's an issue, it might be worth setting 2 variables to the required period start/end dates.
-
"it might be worth setting 2 variables to the required period start/end dates."
That's more or less what Joe Nakanishi suggested, isn't it?
-
>>That's more or less what Joe Nakanishi suggested, isn't it?
Nope. That suggestion was for 2 CONVERT()'s in the WHERE, which precludes index usage.
>>"it might be worth setting 2 variables to the required period start/end dates."
I meant declaring 2 local variables and computing necessary values into them before the SELECT, then using the variables in the WHERE so indexes might be used.
-
PW, "Yes, but you don't need that user-defined function", true. The functions was provided because of Kenn's "I want to totally ignore time and only look at the date".
And of course "SQL's DateDiff works on day boundaries if days is the requested interval" - that's how the UDF works.
I have seen your post before (with the DATEDIFF solution) and decided that Kenn just needs a different point of view. Sometimes when you look at stuff from a different angle you figure out that the solutions provided to you work fine but you just messed up the implementation.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
PW, you are saying that
declare @StartDate datetime
declare @EndDate datetime
select @StartDate = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 11, 101), 101)
select @EndDate = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 10, 101), 101)
SELECT ARJOBHD.INVNO, ARJOB.PRICE, ARJOB.FRTAMT, ARJOBHD.INVDATE
FROM ARJOB INNER JOIN
ARJOBHD ON ARJOB.INVNO = ARJOBHD.INVNO
WHERE ARJOBHD.INVDATE > @StartDate
AND ARJOBHD.INVDATE < @EndDate
is faster than
SELECT ARJOBHD.INVNO, ARJOB.PRICE, ARJOB.FRTAMT, ARJOBHD.INVDATE
FROM ARJOB INNER JOIN
ARJOBHD ON ARJOB.INVNO = ARJOBHD.INVNO
WHERE ARJOBHD.INVDATE > CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 11, 101), 101)
AND ARJOBHD.INVDATE < CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 10, 101), 101)
Or do I misunderstand you?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply