Hello Everybody!
I am looking for help to accomplish following task in T-SQL: I have a timezone, and I need to calculate an UTC datetime which is current date minus one month, but has 0 hours and 0 minutes in a given timezone.
views:
16answers:
2
A:
Is this what you need?
Select Cast(Floor(Cast(DateAdd(month, -1, getutcdate()) as float)) as datetime)
Barry
2010-06-24 10:34:40
Does this return UTC date with 0 a.m.? I need 0 a.m. in a given timezone.
DarkDeny
2010-06-24 10:44:53
It returns the UTC date, takes a month off and then strips out the time portion so it will return something like this `2010-05-24 00:00:00.000`
Barry
2010-06-24 10:47:32
I need to have exact UTC datetime, which when converted to given timezone is the beginning of the given day.For example, 2010-05-24 02:00:00.000 would be that result, that I need, in case of timezone -02:00.
DarkDeny
2010-06-24 10:52:06
Ah sorry, I have misunderstood your question.
Barry
2010-06-24 11:04:37
A:
SQL Server 2008 and later provides the datetimeoffset type that includes a timezone. You can change the timezone using the SWITCHOFFSET function to change timezone and the DATEADD function to add -1 month to the resulting value. Then you can convert to the DATE type to eliminate the time part.
select cast(DATEADD(month,-1,todatetimeoffset(GETUTCDATE(),'+02:00')) as DATE)
Panagiotis Kanavos
2010-06-24 10:46:15
If I cut time part as you propose, will this be 0a.m. in a given timezone?I need to have exact UTC datetime, which when converted to given timezone is the beginning of the given day.
DarkDeny
2010-06-24 10:50:45
The DATE type has NO time part. If you convert it back to another date type (e.g. DATETIME) it will have a time part of 00:00. Conversions between datetime, date, datetime2, datetimeoffset are implicit which means that you can assign the DATE value to a DATETIME variable or store it in a DATETIME column and it will automatically get the 00:00 time part. Note that only datetimeoffset2 has a timezone. If you want the resulting date to have 00:00 for a specific timezone only, you should use a final ToDateTimeOffset to convert the DATE to the desired timezone
Panagiotis Kanavos
2010-06-24 11:16:54
I can't figure out yet, how can I switch my datetime from given timezone to UTC. Can you explain this please?
DarkDeny
2010-06-25 10:44:01