June 30, 2007 at 2:44 pm
Hello, I need help with a Stored Procedure. I have the following script. I need to find out if the column is less then 30 days from now.
What I need: identifiy the certified individuals whose five-year certification renewal cycle expires in one month.
Here is the script
I went into the database and put an expiration date of 6/30/07 in for a record and it's not pulling it.
---------------SCRIPT------------------------------
SELECT
co_customer.cst_sort_name_dn, ce_cert_program.cpg_code, ce_cert_program.cpg_ccp_key, ce_certificant.crt_expiration_date,
ce_certificant
.crt_certification_date, ce_certificant.crt_end_date
FROM
co_customer INNER JOIN
ce_certificant
ON co_customer.cst_key = ce_certificant.crt_cst_key INNER JOIN
ce_cert_program
ON ce_certificant.crt_cpg_key = ce_cert_program.cpg_key
where
datediff(dd, crt_expiration_date, getdate()) <30
Any help would be greatly appreciated
June 30, 2007 at 4:55 pm
well I got further, I didn't need a datediff, just needed the following
......
Where crt_expiration_date - getdate() < 30
that did the trick
June 30, 2007 at 10:37 pm
Close... very close... but, that way will prevent an Index Seek if have a usable index on the column(s) because you're doing a calculation (subtraction) on the column. Best you'll ever get out of it is an Index Scan which can be almost as bad (sometimes, worse if a bookmark shows up) as a table scan.
Try this, instead...
WHERE Crt_Expiration_Date < GETDATE() + 30
If you think it doesn't matter because you don't have an index, please think again... if you ever do add an index, do you want to go back and fix all existing code to use it?
--Jeff Moden
July 2, 2007 at 12:02 am
Any feedback, Stephen?
--Jeff Moden
July 2, 2007 at 12:35 pm
Jeff -
Thanks for pointing this out. I had a extremely data intensive query with millions of rows and i was guilty of running the date calculation on the column instead of the value i was comparing it to. I'm still testing, but it looks faster on first glance. Thanks for the tip!
Ryan
July 2, 2007 at 12:38 pm
Yes, thank you for that, I usually forget about the indexing stuff and we are in a extreme crunch on these reports. I really appreciate the input. Great communication on your part.
July 2, 2007 at 2:09 pm
The recommendation to keep the WHERE clause sargable was sound. However, I try never to use direct datetime manipulation. Also there is another thing to consider.
The way to write the WHERE clause is:
WHERE crt_expiration_date < DateAdd( dd, 30, GetDate())
However, be aware that this comparison will take into account the time part of the two dates. So if you run this early in the morning, it will miss expiration dates that are indeed 30 days from now but with a time value later in the day. This may or may not matter to your application. If it does, a way around it -- while still keeping your clause sargable -- is to add another day but truncate the time part:
WHERE crt_expiration_date < Convert( varchar, DateAdd( dd, 31, GetDate()), 101 )
This will catch all values within 30 days from now right up to midnight of the 31st day.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
July 2, 2007 at 2:43 pm
>>WHERE crt_expiration_date < Convert( varchar, DateAdd( dd, 31, GetDate()), 101 )<<
Keep in mind that such construct generates a varchar that will have to be converted "implicitly" back to datetime
* Noel
July 2, 2007 at 3:47 pm
WHERE crt_expiration_date < DateAdd(day, datediff(day, 31, current_timestamp), 0)
N 56°04'39.16"
E 12°55'05.25"
July 2, 2007 at 5:39 pm
Yes, but also keep in mind that this is executed once for each query, not once for each row. At least -- it should! If I am wrong about that (does the call to GetDate make a difference?), you can always store the result in a variable and it would look like:
WHERE crt_expiration_date < @CutoffDate
Btw, I really like Larsson's method. No conversion.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
July 3, 2007 at 9:19 am
>>WHERE crt_expiration_date < Convert( varchar, DateAdd( dd, 31, GetDate()), 101 )<<
This could also fail if the setting of DATEFORMAT is not MDY, which means on systems that are non-US English, or where the user'd default launguage setting is not US English, or there has been an explicit SET DATEFORMAT.
Peters's way is much better, and no harder to write.
July 3, 2007 at 9:38 am
Actually, we have a function, TruncTime, which we use. I changed it to one of the more often used hardcoded methods for simplicity. I guess I should have just left it and added "this function truncates the time portion from the date." Ultimately it would have generated less traffic.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply