SQL to get first day of month and last day of month but for 2 months ago

Database

SQL to get first day of month and last day of month but for 2 months ago

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:

select TableA.thedate   add_months(current_date - (extract(day from current_date)-2),-1) as BOM,

  current_date - extract(day from current_date) as eom

  from TABLE AS TableA

  where

  TableA.TheDate between

  add_months(current_date - (extract(day from current_date)-2),-1) 

  and current_date - extract(day from current_date)

========================== BOM (Begin of Month)=== EOM (End of Month)

                            BOM          EOM

My result shows 5/2/2014 5/31/2014

I am confusing on how to position the intervals correctly to go back that far.

Cappertan

8 REPLIES

Re: SQL to get first day of month and last day of month but for 2 months ago

Hi

Please try below query.

SEL ADD_MONTHS((DATE - EXTRACT(DAY FROM DATE)+1), -2) BOM , ADD_MONTHS(LAST_DAY(DATE),-2) EOM ;

Thanks

Santanu

Re: SQL to get first day of month and last day of month but for 2 months ago

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.

select  add_months(last_day(date'2014-09-15'),-1),last_day(add_months(date'2014-09-15',-1));

  ADD_MONTHS(last_day(2014-09-15), -1)      last_day(ADD_MONTHS(2014-09-15, -1))

1 2014-08-30                                2014-08-31

Re: SQL to get first day of month and last day of month but for 2 months ago

 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.

  FDo2MAGO    LDo2MAGO

----------  ----------

2014-07-01  2014-07-31

HTH

Cheers.

Carlos.

Re: SQL to get first day of month and last day of month but for 2 months ago

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:

SampleDate - EXTRACT(DAY FROM SampleDate) + 1

End of the Month:

ADD_MONTHS(SampleDate - EXTRACT(DAY FROM SampleDate) + 1, 1) - 1

Additional details about the implementation are available at this link:

http://www.sqltactics.com/how-to-find-the-last-day-of-the-month-in-sql-mysql-postgresql-and-teradata...

N/A

Re: SQL to get first day of month and last day of month but for 2 months ago

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

Re: SQL to get first day of month and last day of month but for 2 months ago

Hi Dieter,

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(ADD_MONTHS(CURRENT_DATE,-2),'mon')
N/A

Re: SQL to get first day of month and last day of month but for 2 months ago

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 :-)

Re: SQL to get first day of month and last day of month but for 2 months ago

Hi ,

I have data as below in my teradata table







Table:cust_clm              
custId grpnbr   fromdt  thrudt  chronind  caseid  srvid  inpind basenbr
10 100 12/1/2008 12/31/2008 Y 111 121 N 20
10 100 12/1/2008 12/10/2008 Y 110 120 null 30
10 100 12/2/2008 12/20/2008 Y 112 122 null 40
10 100 12/1/2008 12/31/2008 Y null 123 null 45
10 100 12/2/2008 12/31/2008 Y null 124 null 50
10 101 11/1/2008 12/31/2008 Y 116 null N 55
10 101 11/2/2008 11/30/2008 Y 117 118 N 60
10 101 11/2/2008 11/30/2008 Y 119 120 N 65
10 101 11/2/2008 11/30/2008 Y null null   78






output:                
custId grpnbr   fromdt  thrudt  chronind  caseid  srvid  inpind basenbr
10 100 12/1/2008 12/31/2008 Y 111 121 N 20
10 100 12/1/2008 12/10/2008 Y 110 120 null 30
10 101 11/2/2008 11/30/2008 Y 117 118 N 60

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.