TSQL question with Datediff

Question

Post reply

TSQL question with Datediff

Stephen crocker

SSCrazy

Points: 2512
More actions
June 30, 2007 at 2:44 pm

#179600

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

Viewing 12 posts - 1 through 11 (of 11 total)

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

Stephen crocker SSCrazy Points: 2512 More actions · Answer 1

well I got further, I didn't need a datediff, just needed the following

......

Where crt_expiration_date - getdate() < 30

that did the trick

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 2

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

Any feedback, Stephen?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ryan Sorensen Grasshopper Points: 13 More actions · Answer 4

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

Stephen crocker SSCrazy Points: 2512 More actions · Answer 5

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.

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 6

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

noeld SSC Guru Points: 96590 More actions · Answer 7

>>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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 8

WHERE crt_expiration_date < DateAdd(day, datediff(day, 31, current_timestamp), 0)

N 56°04'39.16"
E 12°55'05.25"

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 9

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 10

>>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.

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 11

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