BTEQ variables

[dpit]

I want to write a script that looks back at the last month for a sql query.

so how do I store todays date and make two more dates for beginning and end of last month.

thanks,

 

[BillDHS]

Use extract function to get the first day of current month, then identify the last month based on that day.

For example:

LastFullMonth >= ADD_MONTHS(
CURRENT_DATE - (EXTRACT(Day FROM CURRENT_DATE)- 1), -1)
AND

LastFullMonth <=
CURRENT_DATE - (EXTRACT(Day FROM CURRENT_DATE))

The system calendar table is another resource for identifying time periods.

 

[tdgulbra]

I think this is easier in SQL than BTEQ variables.

To store today's date just use the keyword DATE in an insert statement. (sel DATE



To get to the first day of the current month. Teradata dates are stored as integers so you can use some tricky math to get to day 1 of this month:

sel (((date/100)*100)+1)(date);

To get to the last day of the current month, you can also use math. Basically you go to the first day of next month and let Teradata date math handle the subtraction.

sel ((((((date/100)+1)*100)+1)(date)) - 1) (date);

I hope this helps
Tony