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
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?
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:
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.
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
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.
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?
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'.
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;