October 11, 2002 at 9:17 am
Ok, thanks for everyone's help on my previous question. I abandoned the idea of using a trigger and instead i'm going with
job/stored procedure scenario. I ran into a snag however (there is a fricking surprise!)
and it relates to the getdate function.
I'm having my stored procedure delete all records that contain a date that is less then
the date provided by getdate.
The problem is that getdate provides a long date (dd/mm/yyyy hh:mm:ss) while my date in
the table is in a short date format (dd/mm/yyyy) therefore all records with todays date (example) get deleted today instead of tommorrow. SO.... the question is
how do i get a date in short format?
i tried building it myself using datepart
and '/' but the '/' causes an error when the procedure runs. Any ideas?
October 11, 2002 at 9:30 am
use coldate < dateadd('d', 1, getdate())
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 11, 2002 at 9:31 am
Try the CONVERT command.
October 11, 2002 at 10:25 am
I'm thinking that Allen has the better solution. If I understand szopdog correctly, he does not want to delete todays records, but everything less than todays records.
Steve's answer would delete todays records.
I would use the convert or Cast to convert the date to a short date for the compare.
Am I misunderstanding?
If your not gonna help, Please move to the side, because if your not helping the situation, your hurting it....
October 11, 2002 at 10:30 am
Mine will delete everything less than tomorrow, which includes today's records.
I did misread. You'd want to delete everything less than today, so you can take
cast( month(getdate()) + '/' day(getdate()) + '/' + year(getdate()) as datetime)
to get today at 12:00:00am. You praobably need to cast the month, day, and year values to char as well.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 11, 2002 at 11:16 am
To elaborate on Allen's you can do
CONVERT(CHAR,GETDATE(),101) and it will return the date without the time as ytou requested.
Steve's works great and actually doesn't do one read that is performed by the CONVERT function this way. But the CONVERT is easier to read and keep up with in logic.
Also a note, if you have any records that would occurr around Midnight you might want to use DATEADD with the millisecond option to remove 3 milliseconds, there is an odd rounding in SQL when comparing values in millisecounds to each other that can overlap times and cause things to be kept to disappear. Or use CONVERT(CHAR,coldate,101) to make sure does not occurr and as long as you are doing < they should be safe. Otherwise if no data wih time near midnight don't worry.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply