View User Profile

Comment: Re: Understanding the SalesLogix 6.2 Connection String
I spotted that one. Unfortunately the SQL getutcdate() cannot be used within a function.

What I'm really looking for is the Table of timezones SLX recognises. If you look at the USERINFO table you get a short description of the timezone the user is in. I suspect SLX hasnt made this very sophisticated with a table of Daylight savings zones etc.

Anyway, Try this one we will use

ALTER FUNCTION [SYSDBA].[UTC_LocalTime] (@UTCTime DateTime, @USERID CHAR(12))
RETURNS DateTime AS

-- SELECT [SYSDBA].[UTC_LocalTime] ('10/25/2004 6:29:39 PM','ADMIN')

BEGIN
DECLARE @Year Int
SELECT @Year = DatePart(Year,@UTCTime)
Declare @TimeZone varchar(50)
Declare @LocalTime datetime
DECLARE @BegApr varchar(30)
DECLARE @EndOct varchar(30)
SET @BegApr = Convert(varchar,@Year)+'-04-01 00:00:00'
SET @EndOct = CONVERT (varchar,@Year)+'-10-31 00:00:00'
--checks the first day of month in relat
-- ion to the first Sunday of the month. Se
-- ts @AprDay to the appropriate date. For
-- Daylight savings.
DECLARE @AprDay Int
SET @AprDay =
CASE Datepart(Weekday,@BegApr)
WHEN 1 THEN 01
WHEN 2 THEN 07
WHEN 3 THEN 06
WHEN 4 THEN 05
WHEN 5 THEN 04
WHEN 6 THEN 03
WHEN 7 THEN 02
END

DECLARE @DSTStart DateTime
SET @DSTStart = Convert(DateTime,Convert(varchar,@Year)+'-04-'+Convert(Varchar,@AprDay)+' 07:00:00')
--checks the last day of month in relati
-- on to the last Sunday of the month. Sets
-- @OctDay to the appropriate date. For Da
-- ylight savings.
DECLARE @OctDay Int
SET @OctDay =
CASE Datepart(Weekday,@EndOct)
WHEN 1 THEN 31
WHEN 2 THEN 30
WHEN 3 THEN 29
WHEN 4 THEN 28
WHEN 5 THEN 27
WHEN 6 THEN 26
WHEN 7 THEN 25
END

-- default to Central if the user doesnt have a timezone in the database.
select @timezone = isnull((select timezone from userinfo where userid=@userid),'Central Standard Time')

DECLARE @DSTEnd DateTime

SET @DSTEnd = Convert(DateTime,Convert(varchar,@Year)+'-10-'+Convert(Varchar,@OctDay)+' 06:00:00')
DECLARE @OffSet DateTime

-- NON-Daylight savings adjustments.
SELECT @LocalTime =
(CASE @timezone
WHEN 'Eastern Standard Time' THEN DATEADD(hh, - 5, @UTCTime)
WHEN 'Central Standard Time' THEN DATEADD(hh, - 6, @UTCTime)
WHEN 'Mountain Standard Time' THEN DATEADD(hh, - 7, @UTCTime)
WHEN 'Pacific Standard Time' THEN DATEADD(hh, - 8, @UTCTime)
ELSE DATEADD(hh, - 6, @UTCTime)
END)

-- Daylight savings adjustments.
IF @UTCTime >= @DSTStart AND @UTCTime <= @DSTEnd
Begin
SELECT @LocalTime =
CASE @timezone
WHEN 'Eastern Standard Time' THEN DATEADD(hh, - 4, @UTCTime)
WHEN 'Central Standard Time' THEN DATEADD(hh, - 5, @UTCTime)
WHEN 'Mountain Standard Time' THEN DATEADD(hh, - 6, @UTCTime)
WHEN 'Pacific Standard Time' THEN DATEADD(hh, - 7, @UTCTime)
ELSE DATEADD(hh, - 5, @UTCTime)
END
End

RETURN @LocalTime
END

Author: David van der Linden - 11/8/2004

Comment: Re: Understanding the SalesLogix 6.2 Connection String
Where is the slx_timezonelist stored procedure stored?

Does anyone have a good function/procedure for converting from GMT to Local time.

Most of our reports are written with sprocs and hence cant use the OLE Provider to do the timezone conversion.

David

Author: David van der Linden - 11/8/2004

slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation. This site, and all contents herein, are Copyright © 2025 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.	code of conduct \|

page cache (param): 10/31/2025 4:52:01 PM