4

In my web app, all date values are converted to UTC before being passed into the SQL database and then converted back to local time whenever they are displayed in the app. So the bottom line is that all dates are in UTC in the database.

That worked just fine until I started report development. Crystal Reports has no ability (that I can detect) to do a time zone conversion so I need to do the conversion within SQL before passing the value to the report.

SQL Server appears to have a much more limited ability to convert datetime values. The only function I see is SWITCHOFFSET. The problem with this function is that it is not daylight-savings aware which seems to be a major flaw. In asp.net, I can pass a datetime value with a time zone and it will automatically convert it to UTC time, taking account of any needed daylight savings time adjustments.

In SQL though, instead of saying something like 

SWITCHOFFSET (SomeDate,"Eastern Time"),

I now have to say 

SWITCHOFFSET(SomeDate, "-4:00").

But what happens if I'm pulling data from a table and I ask for all of the data for March, the month when daylight savings starts? No matter what, some of it will be incorrect. I can either say -4:00 or -5:00 but obviously, not both.

Is there some other function to adjust for this as, frankly, it seems SWITCHOFFSET is half baked if it doesn't know how to do a daylight savings conversion.

Jon Leach
  • 821
  • 2
  • 10
  • 21

2 Answers2

1

The only way I can think of is:

  • Use the C# integration to create a method for that ;) Thank heaven you can fall back to that.

  • Use VIEWS to run reporting against them. ALWAYS a good idea anyway as it decouples the reports (outside your direct control) from the database schema, which is an implementation detail. I normally have a reporting SCHEMA that has all the views for reports as another documented API.

Last but not least you COULD try getting rid of SQL Access and use an OData API, but that is some work.

Anyhow, the views can use the method you programmed to convert the time.

Adam Porad
  • 14,193
  • 3
  • 31
  • 56
TomTom
  • 61,059
  • 10
  • 88
  • 148
  • Unfortunately, I'm on Azure and it doesn't support CLR. – Jon Leach Jul 06 '12 at 19:41
  • 2
    Then, sir, you have a problem. Simply like that. One reason I stay away as hell from Azure - the other being the high price and the inability of large databases. – TomTom Jul 06 '12 at 19:51
  • I don't see it as an Azure Problem. I think it's a timezone problem, in general, and being in a multi-tenant environment makes the issue more evident. – 1c1cle Dec 30 '13 at 21:52
0

For those who use SSRS Reports, you may be able to detect the TimeZone in SSRS. Refer to the post here for more info.

Working with Time Zones in SSRS

Community
  • 1
  • 1
Jeganinfo
  • 324
  • 3
  • 5