Help with creating your own From and To dates

Toad Data Point & Toad Intelligence Central
1

AND dateadd (wk, -2, GetDate ()))

Group By used to return one row of data, so a date is not included in the output.

To display a From and To dates in my report I thought of creating two calculated fields.

FromDate - with expression

dateadd (wk, -6, GetDate ())

ToDate - with expression

dateadd (wk, -2, GetDate ())

However when I tried creating the FromDate calculated field I received the error message “The specified expression contains invalid symbols (line 1, character 22).”

Which is the closing bracket of the GetDate…

I have even tried changing GetDate() to Today().

dateadd (wk, -6, Today ())

This saved OK in the Expression Editor with no error message. But after placing this calculated field at ReportHeaderBand1 level in my report nothing is displayed when Previewed.

Any help and advise welcome.

Thank you in advance.

David

I have an SQL script (database is SQL Server) that filters four weeks of data by including the following in the Where clause

([PLTC].[Week Ending] BETWEEN dateadd (wk, -6, GetDate ())

2

It looks like the calculated fields want to use only the functions they list in the expression builder and must use a database field of type date to work.

IE:
GetDate(DateTime)
Extracts a date from the defined DateTime.

Instead I would add two columns in your query to be used for your dates. Something like this.

SELECT shipping_address_id,
order_id,
dateadd (yy, -10, GetDate ()) begindate,
dateadd (yy, -9, GetDate ()) enddate
FROM orders
WHERE ORDER_DATE BETWEEN dateadd (yy, -10, GetDate ())
AND dateadd (yy, -9, GetDate ())

Debbie

3

I’m trying to do a rolling 13 months and I keep getting an error.

SELECT VCTD485.EXT_KEY1,

VCTD485.COMP_CAN_DT

From ASKME_CPNI_UNRESTRICTED_VIEWS.VCTD485 VCTD485

WHERE VCTD485.COMP_CAN_DT >= DATEADD(Month, -13, GetDate())

[Teradata Database] [3706] Syntax error: expected something between ‘(’ and the ‘Month’ keyword.

Not sure what is missing.

Aaron

4

Hi Aaron, give the following a try:

SELECT VCTD485.EXT_KEY1,
VCTD485.COMP_CAN_DT
From ASKME_CPNI_UNRESTRICTED_VIEWS.VCTD485 VCTD485
WHERE VCTD485.COMP_CAN_DT >= ADD_MONTHS( CURRENT_DATE , -13);

as info, here’s where I found some posts that helped me provide this answer: community.teradata.com/…/73984

and here: http://forgetcode.com/Teradata/1616-Getting-Current-date-and-time