Where did I go wrong with this SQL Query?
-
I want to check for records that were created today and are one hour old but not two hours old.
This is what I have come up with but of course it does not work.
(DATEDIFF(hh, datesent, { fn CURTIME() }) > 1)
AND (DATEDIFF(hh, datesent, { fn CURTIME() }) < 2)
AND (DATEDIFF(DD, datecreated, { fn CURDATE() })
= { fn NOW() }) AND (accepteddate IS NULL)
-
You have three different datecolumns in there... Does all three have something to do with the age?
If datecreated is what signifies the one-two hour limit, then this is one way to say where datecreated between 2 hours back and 1 hour back...
WHERE datecreated BETWEEN DATEADD(hh, -2, GETDATE()) AND DATEADD(hh, -1, GETDATE())
/Kenneth
-
YES!!..That worked very well..Thank you very much.
Can we take this one step further...Now that we found the record that is one hour old, how to I put a value of "1"into a field call "SENT" into that record. Also I think I will have to run this query as a job to be run like every 15 mins or so.
The final thing that this will do is send out a email to the personthat this record is dispatched to, but I think I have that part figured out.
-
I think that I have to use the command "SET" but not really sure how to use it.
-
Update <tablename> set sent = 1 where datecreated BETWEEN DATEADD(hh, -2, GETDATE()) AND DATEADD(hh, -1, GETDATE())
-
Since this seems to be more than one step
(find the rows, send mail, update sent to 1 for those (successfully(?) sent)
just be careful not to miss any in your 'batch' or let some new slip in in between while you're working on the find-send-update sequence of events.
/Kenneth
-
Thank You Kenneth....That is definatley something to keep in mind.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply