11/21/2024 3:50:37 PM
slxdeveloper.com
Now Live!
|
|
| |
|
User Profile -
David van der Linden
|
|
|
David van der Linden
(SalesLogix Business Partner)
Cyberonics
fjrigjwwe9r1SiteUser:UserBio fiogf49gjkf0d
|
|
| Log in to send this member a
message! |
|
|
| David van der Linden's
Contributions |
|
David van der Linden
has contributed
comments and
0
articles.
|
| |
| Select to view:
|
Comment: Re: Understanding the SalesLogix 6.2 Connection String
fiogf49gjkf0d 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
fiogf49gjkf0d 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
|
|
|
|
|
|
|
|