If I could add something to what Jim mentioned. In SQL Server functions
(UDFs) can not return non-deterministic values "directly". You can
accomplish the goal of using or returning non-deterministic values (ie.
GetDate(), GetUTCDate()) within a UDF by placing the non-deterministic
values in a SQL View, and then querying that SQL View.
For example:
CREATE VIEW dbo.udv_GetDate
AS
SELECT GETDATE() GetDate
In the UDF, you use "SELECT @GetDate = GetDate FROM udv_GetDate" to
obtain the non-deterministic value.
Here is another SQL View that is useful for getting the time zone offset
of the ARS Server:
CREATE VIEW dbo.udv_TimeZoneOffset
AS
SELECT DATEDIFF(hh, GetUTCDate(), GETDATE()) AS TimeZoneOffset
Here is a function I use frequently with ARS programming.
CREATE FUNCTION dbo.udf_RemedyTimeToRegularTime
(
@RemedyTime INT
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(s, @RemedyTime + (dbo.udv_TimeZoneOffset() *
3600), '01/01/1970')
END
Usage in a Set Fields SQL: SELECT
dbo.udf_RemedyTimeToRegularTime($Create Date$)
Stephen
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Jim Ashton
Sent: Friday, December 08, 2006 3:23 PM
To: [email protected]
Subject: Re: Retrieving Date from SQL
Yet another option....
The following T-SQL functions convert date/time to/from Remedy and
account for GMT and Daylight Savings Time (set up for Canada):
/* NOTES on this function:
Input parameters @GetUTCDate and @GetDate expect the results of the
GetUTCDate() and GetDate()
built-in functions respectively. These functions won't work within the
context of
a UDF.
@RemedySeconds is the value from a Remedy date field.
SAMPLE USE:
declare @result datetime
declare @remedyseconds int
select @remedyseconds=1140120203 --create_date from shr_categorization
where entry_id=000000000001583
select
@result=msdb.dbo.fnGetRemedyDate(@remedyseconds,GetUTCDate(),GetDate
())
print @result
Acknowledgements:
http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i-
convert-local-time-to-utc-gmt-time.html
*/
USE MSDB
GO
ALTER FUNCTION fnGetRemedyDate(@RemedySeconds int, @GetUTCDate datetime,
@GetDate datetime)
RETURNS datetime
AS
BEGIN
DECLARE
@sdt smalldatetime,
@edt smalldatetime,
@i tinyint,
@Offset int,
@RemedyDate datetime,
@UTCDate smalldatetime
SELECT @UTCDate = DATEADD(second, @RemedySeconds, '1970-01-01') --
value of Remedy (epoch) time GMT
-- find first Sunday in April
SET @i = 1
WHILE @i < 7
BEGIN
SET @sdt = RTRIM(YEAR(@UTCDate))+'040'+RTRIM(@i)+' 02:00'
IF DATEPART(weekday,@sdt)=1
BEGIN
SET @i = 7
END
SET @i = @i + 1
END
-- find last Sunday in October
SET @i = 31
WHILE @i > 24
BEGIN
SET @edt = RTRIM(YEAR(@UTCDate))+'10'+RTRIM(@i) + ' 02:00'
IF DATEPART(weekday,@edt)=1
BEGIN
SET @i = 24
END
SET @i = @i - 1
END
SELECT @Offset = DATEDIFF ( ss , @GetUTCDate , @GetDate )
/* NOTE that the adjustment below is slightly flawed; you are comparing
the
initial value in GMT/UTC time
to the start/end times of DST in the local time zone (since they can't
be
adjusted for UTC without knowing
the adjustment factor)!
A more accurate (but also complex) methodology involves use of the
registry
as described at
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?
txtCodeId=544&lngWId=5
*/
IF (@UTCDate>[EMAIL PROTECTED] AND @UTCDate<@edt)
SET @offset = @offset + 3600 -- an hour's worth of seconds
SELECT @RemedyDate = DATEADD(second,(@RemedySeconds +
@Offset), '1970-01-01')
RETURN(@RemedyDate)
END
/*
SAMPLE USE:
declare @TargetDate datetime
declare @RemedyDate datetime
set @TargetDate='2006-02-09'
select
@RemedyDate=msdb.dbo.fnSetRemedyDate(@TargetDate,GetUTCDate(),GetDate
())
print @RemedyDate
*/
ALTER FUNCTION fnSetRemedyDate(@TargetDate datetime, @GetUTCDate
datetime,
@GetDate datetime)
RETURNS int
AS
BEGIN
-- work out GMT/UTC offset value
DECLARE @Offset int,
@sdt smalldatetime,
@edt smalldatetime,
@i tinyint
-- find first Sunday in April
SET @i = 1
WHILE @i < 7
BEGIN
SET @sdt = RTRIM(YEAR(@TargetDate))+'040'+RTRIM(@i)+' 02:00'
IF DATEPART(weekday,@sdt)=1
BEGIN
SET @i = 7
END
SET @i = @i + 1
END
-- find last Sunday in October
SET @i = 31
WHILE @i > 24
BEGIN
SET @edt = RTRIM(YEAR(@TargetDate))+'10'+RTRIM(@i) + '
02:00'
IF DATEPART(weekday,@edt)=1
BEGIN
SET @i = 24
END
SET @i = @i - 1
END
SELECT @Offset = DATEDIFF ( hh , @GetDate, @GetUTCDate )
-- adjust for DST (see NOTE in fnGetRemedyDate)
IF (@TargetDate>[EMAIL PROTECTED] AND @TargetDate<@edt)
SET @Offset = @Offset - 1 -- an hour's worth of seconds
--apply offset value to target date (so date is entered in GMT time, not
local time)
SELECT @TargetDate = dateadd(hh,@Offset,@TargetDate)
-- determine Remedy int value for revised target date
DECLARE @RemedyDate int
SELECT @RemedyDate=ABS(DATEDIFF(ss,@TargetDate,'1970-01-01'))
RETURN(@remedyDate)
END
/*
SAMPLE USE:
declare @result int
declare @GetUTCDate datetime
select @result=msdb.dbo.fnSetRemedyDate(GetUTCDate())
print @result
*/
CREATE FUNCTION fnSetRemedyCurrentDate(@GetUTCDate datetime)
RETURNS int
AS
BEGIN
DECLARE @RemedyDate int
SELECT @RemedyDate=ABS(DATEDIFF(ss,@GetUTCDate,'1970-01-01'))
RETURN(@remedyDate)
END
________________________________________________________________________
_______
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
