All Forums Analytics
zskuza 15 posts Joined 04/11
07 Jan 2015
Syntax error due to Stored Proc Parameter

The following procedure can be called with no errors when the Procedure input parameter is not used in the WHERE clause, but gives the following Syntax error when the input parameter is used:


3706: Syntax error: expected something between a string or a Unicode character literal and the 'AT' keyword.


Can someone help?








CAST(CAST(((Completed_Dt AT TIME ZONE 'gmt') AT TIME ZONE 'Australia Eastern') AS VARCHAR(10)) AS DATE) AS ActivityDate


WHERE ActivityDate BETWEEN ADD_MONTHS(DATE - EXTRACT(DAY FROM DATE) + 1, -Months) AND DATE --Get Activations from start of prior month to current date




zskuza 15 posts Joined 04/11
08 Jan 2015

To work around this, I've changed the Create statement to a string and execute it via dbc.SysExecSQL().
Not ideal though.
I'd also be happy to learn of a more efficient method to convert a UTC timestamp to output in other timezone rather than the CAST method I'm using:
CAST(CAST(((Completed_Dt AT TIME ZONE 'gmt') AT TIME ZONE 'Australia Eastern') AS VARCHAR(10)) AS DATE) AS ActivityDate

Rohan_Sawant 55 posts Joined 07/14
09 Jan 2015

Hi Zkuza,
Can you please share some data of the source table Tbl1 with the table structure?
Rohan Sawant

dnoeth 4628 posts Joined 11/04
09 Jan 2015

When you check DBQL you'll find that a closing bracket is removed from the SQL code submitted within the SP, seems to be a strange parser bug.
What are you trying to do wtih all those CASTs?
Can you show the datatype of Completed_Dt, some example data and the expected result? 


zskuza 15 posts Joined 04/11
11 Jan 2015

Our data warehouse has timestamp data stored in UTC so I like to convert timestamps to AEST/AEDT as soon as possible in my scripts to avoid misinterpretation as some of these UTC timestamp fields would then need to be joined to date fields which are in AEST/AEDT timezone.
Possibly a misunderstanding of how timestamp functions work in Teradata on my part, I would appreciate any assistance you can give me in this regard.
Completed_Dt is TIMESTAMP(6).
Example data would be:
'2014-11-31 12:34:56+00:00'
'2014-11-31 13:34:56+00:00'
The above proc, when passing in the vaule 1, would would return the data for December and January using Australian Eastern timezone.
Therefore the second row would be returned as '2014-11-31 13:34:56+00:00' is '2014-12-01 00:34:56+11:00'. The first row would not be returned.

dnoeth 4628 posts Joined 11/04
11 Jan 2015

This should work, assuming there's no nov. 31 down under und it should december instead :-)

   TIMESTAMP '2014-12-31 13:34:56+00:00' AS Completed_Dt,
   Completed_Dt AT 'Australia Eastern',
   CAST(Completed_Dt AS DATE AT 'Australia Eastern');

 *** Query completed. One row found. 3 columns returned.

             Completed_Dt  Completed_Dt AT TIME ZONE 'Australia Eastern'  Completed_Dt
-------------------------  ---------------------------------------------  ------------
2014-12-31 13:34:56+00:00                      2015-01-01 00:34:56+11:00      15/01/01



zskuza 15 posts Joined 04/11
12 Jan 2015

Thanks Dieter. Really appreciate your help - I have revised the stored procedure with "cast as date at 'Australia Eastern' and no longer get the syntax error.

zskuza 15 posts Joined 04/11
12 Jan 2015

One last question: Is this 11am Australian time or midnight Australian time:
SELECT CAST('2015-01-10 00:00:00' AS TIMESTAMP(0)) AT 'Australia Eastern'
Result: 10/01/2015 11:00:00+11:00
If 11am, what is the most efficient SQL for generating a timezone specific timestamp representing midnight in that timezone?

dnoeth 4628 posts Joined 11/04
13 Jan 2015

Time zones are always hard to deal with :-)
Additionally different systems might return different results based on some internal settings...
The easiest way should be changing the session time zone to 'Australia Eastern'. 

--Either permanently
MODIFY USER xxx AS TIME ZONE = 'Australia Eastern'; 
--or on a session level
SET TIME ZONE  'Australia Eastern';

Then it's a simple 

SELECT TIMESTAMP '2015-06-10 00:00:00' AT LOCAL;


This should be safe in a Stored Proc, as it will always use the current time zone setting when it was created. So other sessions might have different time zone, but the SP still uses 'Australia Eastern'.


You must sign in to leave a comment.