April 11, 2011 at 12:06 am
I'm working with a database that has a specific table holding the extent of daylight saving (DS) for the timezone I'm interested in (Victoria, Australia).
eg: [Table DST_extent]
DST_start and DST_end
2010-10-03 02:00:00.0002011-04-03 02:00:00.000
2011-10-02 02:00:00.0002012-04-08 02:00:00.000
2012-10-07 02:00:00.0002013-04-07 02:00:00.000
etc.....
I have another table of [Activity]'s and need to dynamically calculate if the date of activity is in or out of the DS zone.
If our Australian DS zone didn't bridge years it would be as simple as looking at YEAR(Activity.BeginDate) = YEAR(DST_extent.DST_start)
but I think I need to do something like
DECLARE @courseDate DateTime;
SET @courseDate = '10/25/2010';
SELECT
@courseDate AS courseDate,
[DST_Start],
[DST_End]
FROM [DST_Extent]
WHERE
CASE WHEN MONTH(@courseDate)>9 THEN
YEAR(@courseDate) = YEAR([DST_Start]) END
CASE WHEN MONTH(@courseDate)<5 THEN
YEAR(@courseDate) = YEAR([DST_End]) END
Hope this is not too confusing but I'm sure I can't be the first person wrestling with this type of thing.
Geoff
April 11, 2011 at 6:47 am
just building forward from your query...
What's wrong with :
SELECT
@courseDate AS courseDate,
E.[DST_Start],
E.[DST_End]
FROM [DST_Extent] E
where E.[DST_Start] >= @courseDate
and @courseDate <= E.[DST_End]
this solution has the advantage of being able to use an index more optimal than your function driven version.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 19, 2011 at 3:39 pm
Thanks for getting back so promptly. I feel very rude not responding earlier.
Bad news for me though is that your suggestion didn't resolve the issue. I'll get back later with more specific reasons.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply