Some suggestions please.
So today is June 16, 2014.
I want get my SQL to show April 1, 2014 and April 30, 2014.
I have tried:
Please try below query.
SEL ADD_MONTHS((DATE - EXTRACT(DAY FROM DATE)+1), -2) BOM , ADD_MONTHS(LAST_DAY(DATE),-2) EOM ;
It would be better to put LAST_DAY outside of the ADD_MONTHS.
SEL ADD_MONTHS((DATE - EXTRACT(DAY FROM DATE)+1), -2) BOM , LAST_DAY(ADD_MONTHS(DATE,-2)) EOM ;
Here is the problem with putting it inside.
ADD_MONTHS(last_day(2014-09-15), -1) last_day(ADD_MONTHS(2014-09-15, -1))
1 2014-08-30 2014-08-31
BTEQ -- Enter your SQL request or BTEQ command:
SELECT CAST(((ADD_MONTHS(DATE, -2)/100)*100+1) AS DATE) FDo2MAGO,
CAST(((ADD_MONTHS(DATE, -1)/100)*100+1) AS DATE)-1 LDo2MAGO;
*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.
In case the syntax from Santanu84 above is unclear, here are some options for getting the beginning of the month (BOMONTH) and end of the month (EOMONTH)...
Beginning of the Month:
- ( ) +
End of the Month:
( - ( ) + , ) -
Additional details about the implementation are available at this link:
In TD14 simply use LAST_DAY and TRUNC:
TRUNC(ADD_MONTHS(CURRENT_DATE,-2),'mon') -- first day of month two months ago
LAST_DAY(ADD_MONTHS(CURRENT_DATE,-2)) -- last day of month two months ago
Thanks for sharing TRUNC and LAST_DAY functions.
Why is 'MON' required there in the syntax. In absence of MON, it is returning the date before 2 months but not the first day.
Do we have any other paramaters other than 'MON'?
Do we also have similar patterns for LAST_DAY?
TRUNC is an Oracle-function which strips of the time part of a Timestamp, the same as CAST(col AS DATE) in Teradata.
The optional parameter 'MON' truncates to the first of month, there's also 'Q' = quarter, 'Y' = year, 'W' = week starting on sunday, 'IW' = week starting on monday.
There's no option for LAST_DAY.
Of course you could simply look up those functions in the SQL Functions manual, although it's not really correct fro TRUNC :-)
I have data as below in my teradata table
Requirement: From the above table,I need data for each group of custid,grpnbr,fromdt,chronind
When inipind=N and caseid is not null then min(caseid) row if this is doesnt exists and look for srvid
is not null then min(srvid) row else both does not exists no row returned.
Could you please provide terdata sql query for the above scenario.