Time Zone Adjustment in MS SQL

Here's a quick way to get a local time zone adjustment in SQL. Many apps I have, including Veritas BackupExec and IIS logging write their timestamps in UTC. I'd like my reporting tools to display local time, instead.

Unlike Oracle, there appear to be no built in time zone functions in MS SQL. However, there is a function similar to getdate(), which is getutcdate(). This returns UTC time, and allows you to do a little calculation...

-----code-----

/*
First get the time zone offset, as DATETIME
data. Note that if you were on the other side
of the globe, you'd be adding instead
of subtracting.
*/

DECLARE @etz DATETIME
SET @etz = (SELECT (getutcdate() - getdate())

/*
Now subtract the offset from the date fields.
Or add, if you're on the west side, like I said.
*/

SELECT jobname,
(starttime - @etz) starttime,
(endtime - @etz) endtime,
TotalMB, Rate, Error
FROM JobHistorySummary
WHERE (starttime = (getdate() - 3)

/* Gets the last 3 days of backup history. */

-----code-----

You can't call 'getutcdate()' from within a user defined function, however. But I see nothing that says you can't call it from within a stored procedure... :-)

Later.

CS