Posted - 03/21/2013 : 13:52:40
| Let me start by listing the constraints I'm working under:
- I have read-only access to the database
- I am working in Crystal Reports, using the custom SQL command functionality
- SQL server 2008
- Crystal v11 / 2005
I have the need to determine a UTC datetime given a specific local datetime.
- Given 2/28/13 1:00 AM local (-5 offset) the UTC would be 2/28/13 6:00 AM.
My problem is I do not know the offset and cannot figure a way out to calculate it for a specific datetime.
The current offset is easy...a datediff from a getdate and getUTCdate, but that gives you the wrong offset depending on the datetime.
For instance right now for New York the offset is -4 because we're in daylight saving time, so if I use the getdate/getUTCdate difference I'll get 4, but if I apply that to say...2/28, I'll get the wrong UTC date because on 2/28 the offset was -5 (not daylight saving).
I cannot do this at the app (crystal) level due to the dataset I require, so I'm stuck with a SQL query, and I cannot write a store procedure or function because I have read-only access.
Anyone have any idea how I can accomplish this?