Stored Procedure that runs hourly to assign Day (timezone) values in database

Question

Post reply

Stored Procedure that runs hourly to assign Day (timezone) values in database

metalray

SSCommitted

Points: 1647
More actions
October 1, 2008 at 5:12 am

#194275

I want to write a Stored Procedure that runs hourly to assign Day values in database,
whereas the days should be captured or calculated from the GMT as basis (thats where the server is).
--CalculatedReferenceTable----
TodaysDayGMT: August 5th 2008: Tuesday =2
TodaysDayPMT: August 4th 2008: Monday =1
TodaysDay...: August 6th 2008: Wednesday =3
----RecordsTable----
TimeZone:GMT
Cycle: Every Wednesay =3
SpecialDay: August 5th 2008
TimeZone:PMT
Cycle: Every Friday =5
SpecialDay:NULL
Stored procedure should also hourly check all records
for TodaysDay... and Cycle, and if it matches with today's day, send an email.
Is that a good way for an weekly email reminder ? If so,
did you come accross some sql code that does that for me?
Secondly for special dates another procedure should just check
for the specialdateinserted, timezone and todaysdate in order to
send out an email for this sepcial (on-off) day.
Many thanks for any suggestions.

Viewing 7 posts - 1 through 6 (of 6 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I think I must be missing the point. Why would you run something for a weekly notification, but run it every hour?

There is probably a good reason to do this, but I must be missing something, since I don't see it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

bcronce SSCrazy Points: 2597 More actions · Answer 2

try this and use a case statement

SELECT DATENAME(dw, GETDATE())

edit:

you may want to have another column to precalulate the day

so like

table(reminderid int, dateadded datetime, dayofweek tiny)

then you

insert table

select , GETDATE(), case DATENAME(dw, GETDATE()) when 'sunday' then 0 when 'monday' then 1 ... end

then you SP just looks at the current day and send out a reminder

declare @day tiny

set @day = case DATENAME(dw, GETDATE()) when 'sunday' then 0 when 'monday' then 1 ... end

sendreminder() for each row in (select reminderid from table where dayofweek = @day)

P.S. like my pseudo code?

metalray SSCommitted Points: 1647 More actions · Answer 3

Hi Guys,

thanks a lot.

@GSquared, well I think you have to run it hourly to get some precision into it.

Because at 11:35pm it might be the 3rd of October in London but

its already the 4th of october in Genever. If I run the query only daily at let's say

3pm London time the result for the other time zones might be very unprecise.

@SSC Journeyman.

Great, thanks, that helped a lot !

bcronce SSCrazy Points: 2597 More actions · Answer 4

metalray (10/2/2008)
Hi Guys,
thanks a lot.
@GSquared, well I think you have to run it hourly to get some precision into it.
Because at 11:35pm it might be the 3rd of October in London but
its already the 4th of october in Genever. If I run the query only daily at let's say
3pm London time the result for the other time zones might be very unprecise.
@SSC Journeyman.
Great, thanks, that helped a lot !

actually, because it's based on GMT, it doesn't matter since it just looks that the universal time. I think.....?

metalray SSCommitted Points: 1647 More actions · Answer 5

Hi SSC Journeyman,

if one just uses GMT its fine but what I have done now to get GMT into PMT and others is the following:

UPDATE dbo.TimeZoneAndDateReference SET CurrentDate=DATEADD(hh,4.5,GetDate()) WHERE TimeZone='GMT+04:30'

UPDATE dbo.TimeZoneAndDateReference SET CurrentDate=DATEADD(hh,9,GetDate()) WHERE TimeZone='GMT+09:30'

UPDATE dbo.TimeZoneAndDateReference SET CurrentDate=DATEADD(hh,-6,GetDate()) WHERE TimeZone='GMT-06:00'

...

etc... for all the other timezones

Getdate() will be GMT Greenwhich time (since thats where the server is)

bcronce SSCrazy Points: 2597 More actions · Answer 6

I got mixed up in my thoughts. I was picturing someone selecting a day on a calendar. If you had a calendar on the client side and you transfer everything as GMT from cleint to sever, then it's already translated for you and you don't have to do a thing, but if you just pick a day and not a date, then there's no timezone assciated with it. Personnaly, I think all time being communicated/stored/calculated should always be in universal time/gtm, and not on local time zones.