Need help with date calculation logic

[coghelp]

I have a date field which is in mountin time i.e (GMT: -7 ), I want to make it to CST i.e (GMT: -6)  by taking care of day time saving as well. After converting the date value I need to filter the data to only include if the new calculated value is < 2 PM CST time.

Any idea would be greatly appricated. Please please provide some help.

Thnks
Cog

[Lynn]

In the United States daylight saving time begins on the second Sunday of March and ends on the first Sunday in November. It isn't a trivial date calculation even if you have a date dimension at your disposal.

Does your mountain time in the database honor daylight saving time or is it always GMt -7? If it changes with daylight saving time then just adding 1 hour will always give you correct Central time.

Generally I see date/times stored as GMT which never changes. Then the database ought to have the necessary date and time offsets so the database and the FM model can resolve the inherent complexities. Going down a more complex road on individual reports is not, perhaps, the best course.

[coghelp]

Thanks for the response!!

Nope its not a DWH and I need to use this only for one report, so I am making this change in report directly.

Mountain time also have Day time saving.

so I understand that you want me to include a condition to add +2 in between March 2nd sunday and November 1 sunday and rest is +1. I thought there might me be better sloution than this.

Thanks! waiting for your response

[Lynn]

If Mountain time that is stored also has daylight savings then I think you just need to add an hour without concern for the date because both Mountain and Central observe daylight saving time.

For example, on March 11 this year (just before DST) a mountain time of 10:00 am is equivalent to 11:00 am central time. Plus one hour.

Today (after DST) the clocks have changed in both places and your Mountain time reflects this. So 6:00 am Mountain is 7:00 am Central. Also plus one hour.

Right? Not sure if I'm missing something more complex....

[coghelp]

Sorry I apologise actually it won't change I remember some time it will be 1 hrs and now its again 2 hrs, so I hope it actually not having DTS.

Thanks for your time and help....