All Forums Database
cappertan 1 post Joined 08/04
16 Jun 2014
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

Santanu84 122 posts Joined 04/13
16 Jun 2014

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

ryeldell 1 post Joined 07/14
12 Sep 2014

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

 

CarlosAL 512 posts Joined 04/08
12 Sep 2014

 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.

SQLTactics 1 post Joined 06/15
15 Jun 2015

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/

dnoeth 4628 posts Joined 11/04
15 Jun 2015

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

 

Dieter

nanipsns 7 posts Joined 08/13
13 Aug 2015

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

 

dnoeth 4628 posts Joined 11/04
18 Aug 2015

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

Dieter

srinivas.mmis 5 posts Joined 07/15
18 Aug 2015

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.

Srinivas..

You must sign in to leave a comment.