December 10, 2008 at 4:52 am
Thank you everyone. I am still working on this.
The code
SELECT s.ROW_ID, DATEADD(MINUTE,ISNULL(x.UTC_OFFSET,0),s.Created)
FROM S_SRV_REQ S
LEFT JOIN (SELECT TOP 1 tt.UTC_OFFSET, TT.START_DT
FROM S_TIMEZONE T
JOIN S_TIMEZONE_TEMP TT
ON T.ROW_ID = TT.TIMEZONE_ID
WHERE T.STD_ABBREV = 'Eastern Standard Time'
ORDER BY tt.UTC_OFFSET DESC) AS x
ON s.Created = x.START_DT
returns the error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'S_TIMEZONE_TEMP'.
AND teh code:
ALTER FUNCTION [dbo].[ConvertGMTToEST](@DateTime DateTime)
-- converts the GMT time to EST time based on the siebel S_TIMEZONE and S_TIMEZONE_TMP tables.
-- returns the datetime in EST
returns datetime
as
BEGIN
RETURN DATEADD(MINUTE, ISNULL((SELECT TOP 1 TT.UTC_OFFSET
FROM S_TIMEZONE T JOIN S_TIMEZONE_TMP TT ON T.ROW_ID = TT.TIMEZONE_ID
WHERE T.STD_ABBREV = 'Eastern Standard Time' AND @DateTime = TT.START_DT), 0), @DateTime) AND @DateTime = TT.START_DT
returns the error message:
Msg 156, Level 15, State 1, Procedure ConvertGMTToEST, Line 9
Incorrect syntax near the keyword 'AND'.
December 10, 2008 at 5:11 am
pino_daddy (12/10/2008)
ALTER FUNCTION [dbo].[ConvertGMTToEST](@DateTime DateTime)-- converts the GMT time to EST time based on the siebel S_TIMEZONE and S_TIMEZONE_TMP tables.
-- returns the datetime in EST
returns datetime
as
BEGIN
RETURN DATEADD(MINUTE, ISNULL((SELECT TOP 1 TT.UTC_OFFSET
FROM S_TIMEZONE T JOIN S_TIMEZONE_TMP TT ON T.ROW_ID = TT.TIMEZONE_ID
WHERE T.STD_ABBREV = 'Eastern Standard Time' AND @DateTime = TT.START_DT), 0), @DateTime) AND @DateTime = TT.START_DT
The second block of code
AND @DateTime = TT.START_DT
was not intended as part of the first - it was part of a question.
I'll put it a little differently.
How many dates (column START_DT) are in the table S_TIMEZONE_TMP?
What are the chances that the date passed in to the function (@DateTime) will have a match? If there is no match, then the function will return @DateTime unchanged.
SELECT s.ROW_ID, DATEADD(MINUTE,ISNULL(x.UTC_OFFSET,0),s.Created)
FROM S_SRV_REQ S
LEFT JOIN (SELECT TOP 1 tt.UTC_OFFSET, TT.START_DT
FROM S_TIMEZONE T
JOIN S_TIMEZONE_TEMP TT
ON T.ROW_ID = TT.TIMEZONE_ID
WHERE T.STD_ABBREV = 'Eastern Standard Time'
ORDER BY tt.UTC_OFFSET DESC) AS x
ON s.Created = x.START_DT
We can't do all of the work for you!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 10, 2008 at 7:07 am
Msg 208, Level 16, State 1, Line 1
Invalid object name 'S_TIMEZONE_TEMP'
Then supply the correct table name. You should get in the habbit of supplying the schema too, so that it looks like:
dbo.S_TIMEZONE
Not including the schema will lead to the problem above if you have your tables stored in schema's other than the default.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply