R – SQL Server Reporting Services and UTC time

datetimereporting-services

I've been storing all dates in the database as UTC time for several years now, but recently, it's becoming more of a pain to deal with date range queries in reporting services where the date range is specified in local time.

Specifically, let's take this example data set:

EmailAddress                DateCreated
random_email@example.com    2009-09-01 00:00:00
random_email@example.com    2009-09-01 00:00:00
random_email@example.com    2009-02-28 04:00:00
random_email@example.com    2009-04-27 14:33:00
random_email@example.com    2009-08-31 17:28:00
random_email@example.com    2009-03-19 18:57:00
random_email@example.com    2009-03-01 00:49:00
random_email@example.com    2009-02-28 04:00:00
random_email@example.com    2009-09-01 00:00:00
random_email@example.com    2009-09-16 00:00:00

Now let's say the client wants to see how many records exist for the month of February using central standard time. I cannot simply query for records using UTC time, because the UTC timestamp must be converted to CST before the grouping takes place. In other words, record #7 (2009-03-01 00:49:00) should be counted under February using CST dates, even though the UTC date puts it squarely in the month of March.

What usually ends up happening is that I write a function to convert the date such that a query looks like so:

select 
    dbo.ConvertToLocalDate(DateCreated), 
    count(*) as [Count]
from 
    example_table
group by 
    dbo.ConvertToLocalDate(DateCreated)

but the performance of such a query is less than desirable for anything more than a few hundred thousand rows. I've tried several variations on the theme, including adding a column to pre-calculate the local date, and also a modified query such as:

select 
    t3.LocalDateCreated, 
    count(*) as [Count]
from 
    example_table t1 
        inner join (
            select 
                t2.Email, 
                dbo.ConvertToLocalDate(t2.DateCreated) as LocalDateCreated
            from
                example_table t2) t3 on t3.Email = t1.Email
group by 
    t3.LocalDateCreated

This all seems really hackish to me though. Is there a better way?

Best Answer

Use a persisted computed column in your table to store the Local Date, and then index it and use it in your query.

That way, the value will be calculated when the value is inserted, but you don't need to do the maths yourself anywhere.

Also, read my blog post about Scalar Functions at http://msmvps.com/blogs/robfarley/archive/2009/12/05/dangers-of-begin-and-end.aspx - you may want to rethink the way you're using that function.

Related Topic