Yes, DATEADD() and DATEDIFF()

You can calculate the delta from GMT by saying:

DECLARE delta TINYINT
SELECT delta = DATEDIFF(HOUR, GETDATE(), GETUTCDATE())

Then add or subtract that delta using DATEADD... (which way you go, or
which way you order the functions in the delta calculation, depends on
which side of GMT you are on).

If you are using SQL Server 7.0, there is no GETUTCDATE() function, so you
can use master..xp_regread to get at
HKEY_LOCAL_MACHINE\System\CurrentControlSet\
Control\TimeZoneInformation\ActiveTimeBias (you could schedule this to
happen once daily, and store it in a table, to avoid having to read the
registry in real time for every query).

It gets a little trickier if you observe daylight savings time, and the
dates inside the table might be outside of your current offset (e.g. a
record from January will have a different delta than right now). In this
case, one way is to store the dates where DST changes, and then determine
whether the date in the table is between them or outside of them, and then .
I have done something like this, here in Eastern US we change from 4 hours
to 5 and back again, on the first Sunday in April and the last Saturday in
October:



CREATE TABLE [dbo].[Days] (
[dt] [smalldatetime] PRIMARY KEY CLUSTERED,
[utcOffset] [tinyint] NOT NULL DEFAULT 5
) ON [PRIMARY]
GO

------------------------------------------
-- populate 10+ years of days, + UTCOffset

DECLARE dt SMALLDATETIME, off TINYINT
SELECT dt = '20000101', off = 4

WHILE dt <= '20031231'
BEGIN
--On the first Sunday in April, in the wee hours of the morning, when
--it's 2:00am, set the clock to 3:00am. On the last Sunday in October,
--when it's 2:00am, set the clock to 1:00am (not 3:00 to 2:00).

IF DATENAME(dw, dt)='Sunday' AND DATEPART(DAY, dt)<=7 AND DATENAME(MONTH,
dt)='April'
SET off = 4

IF DATENAME(dw, dt)='Sunday' AND DATEPART(DAY, dt)>=25 AND
DATENAME(MONTH, dt)='October'
SET off = 5

INSERT Days(dt, UTCOffset) VALUES(dt, off)

SET dt = dt + 1
END
GO



Now you can just join on this table (on the dt column) and use the offset
column to calculate a dateadd of HOUR against the real datetime value.

There is some other information, maybe useful, maybe not:

[url]http://www.aspfaq.com/2218[/url]
[url]http://www.aspfaq.com/2334[/url]




" David N" <dq.ninhnetiq.com> wrote in message
news:OoCyZWLRDHA.3880tk2msftngp13.phx.gbl...
>
> Hi All,
>
> I got a bulk insert file sent to me daily that I supposed to load into my
> database. The problem is that the file contains datetime column that was
> recorded using GMT time.
>
> Is there an already built-in SQL function to convert GMT time that I can
> use within my stored procedure?
>
> Thanks.
>
>