IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search Log in to check your private messages Log in
 

DATEADD query trouble


 
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Shripad AVhad

New User


Joined: 02 Sep 2015
Posts: 2
Location: India

PostPosted: Tue Sep 15, 2015 11:57 am
Reply with quote

Hi,
I want to fetch First and last date of the previous month from the 1st day of current month. I am using below query..

Code:
EXEC SQL
     SET :WS-END-DATE   = SELECT DATEADD(D,-1,
          DATEADD(MM, DATEDIFF(M,0,GETDATE()),0))
END-EXEC.


but it isn't working,any solutions?

Code'd
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7312
Location: Inside the Matrix

PostPosted: Tue Sep 15, 2015 12:49 pm
Reply with quote

If it's not working, what is it doing?

If that is supposed to give you two results, how do you intend to get them in one host-variable?

Given the first of a month, you get the first of the previous month by subtracting a month. You get the last of the previous month by subtracting one day.

Whether you need SQL to do this depends. Does your site not have existing "date routines"?

And can you explain in words what you were attempting with your SQL?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Sep 15, 2015 3:08 pm
Reply with quote

db2 isn't SQLServer

Use [URL] BBCode for Links

especially
last_day()
day()
Back to top
View user's profile Send private message
Shripad AVhad

New User


Joined: 02 Sep 2015
Posts: 2
Location: India

PostPosted: Wed Sep 16, 2015 3:34 pm
Reply with quote

Actually i want to extract first and last day of the previous month.
But with this query its giving me error that 'DATEADD is not allowed.' or something.
Do you have any better suggestion?
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL

PostPosted: Wed Sep 16, 2015 3:47 pm
Reply with quote

Shripad AVhad wrote:
Actually i want to extract first and last day of the previous month.
But with this query its giving me error that 'DATEADD is not allowed.' or something.
Do you have any better suggestion?

Yes; read about ADD_MONTHS and LAST_DAY in the fine manual.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1348
Location: Israel

PostPosted: Thu Sep 17, 2015 2:51 pm
Reply with quote

I have this query:
Code:
SELECT                                                             
   CURRENT DATE AS "CURRENT_DATE",                                 
   LAST_DAY(CURRENT DATE) AS "LAST DAY_OF MONTH",                   
   LAST_DAY(CURRENT DATE - 1 MONTH) + 1 DAY AS "FIRST DAY_OF MONTH",
   LAST_DAY(CURRENT DATE) + 1 DAY AS "1ST DAY_NEXT MONTH",         
   LAST_DAY(CURRENT DATE + 1 MONTH) AS "LAST DAY_NXT MONTH",       
   DATE(CONCAT('01.01.',CHAR(YEAR(CURRENT DATE)-1)))               
      AS "1ST DAY_LAST YEAR",                                       
   DATE(CONCAT('31.12.',CHAR(YEAR(CURRENT DATE)+1)))               
      AS "LAST DAY_NEXT YEAR"                                       
FROM                                                               
   SYSIBM.SYSDUMMY1                                                 

It certainly doesn't cover all date manipulations but it gives a good starting point.
Back to top
View user's profile Send private message
Lori A Darling

New User


Joined: 18 Aug 2015
Posts: 2
Location: USA

PostPosted: Thu Sep 17, 2015 6:14 pm
Reply with quote

Here is very simple solution - though not a query - just cobol:

Code:
WORKING-STORAGE
05  WS-LAST-DATE                 PIC 9(08).           
05  WS-LAST-DATER REDEFINES WS-LAST-DATE.             
    10  WS-LAST-CCYY             PIC 9(04) VALUE ZERO.
    10  WS-LAST-MM               PIC 9(02) VALUE ZERO.
    10  WS-LAST-DD               PIC 9(02) VALUE ZERO.



Code:
PROCEDURE DIVISION

 (if current month is 09 and we want first and last date of last month - 08 - then WS-LAST-DATE will be set to 20150801 as we go into this routine)

MOVE 01                            TO WS-LAST-DD         
MOVE WS-CURR-CCYY                  TO WS-LAST-CCYY       
ADD 1 TO WS-CURR-MM                GIVING WS-LAST-MM         
COMPUTE WS-LAST-DD                 = FUNCTION  DATE-OF-INTEGER                               
                                      ( FUNCTION INTEGER-OF-DATE
                                         ( WS-LAST-DATE ) - 1 ) 


Essentially, you give 20150801 as the WS-LAST-DATE.
This code adds 1 to the WS-LAST-MM, making our date 20150901.
Now, it subtracts 1 day from WS-LAST-DATE, giving you the last day of last month - 20150831.

Code'd and de-mangled
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7312
Location: Inside the Matrix

PostPosted: Thu Sep 17, 2015 6:39 pm
Reply with quote

Welcome to the forum.

Please use the Code tags to preserve spacing, and the Preview button to see what your text will look like once submitted.

You get to keep your own code-spacing that way as well :-)
Back to top
View user's profile Send private message
Lori A Darling

New User


Joined: 18 Aug 2015
Posts: 2
Location: USA

PostPosted: Thu Sep 17, 2015 7:14 pm
Reply with quote

I will have to catch up to de-mangling. Thank you for the help!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic All times are GMT + 6 Hours
Forum Index -> DB2
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Forum Replies
No new posts Mainframe ISPREDIT Macro query TSO/ISPF 3
No new posts DFSORT Output file order query DFSORT/ICETOOL 2
No new posts Query on JCL DFSORT/ICETOOL 5
No new posts DB2 Query runs very long DB2 21
No new posts Query to Truncate value in table DB2 2

Back to Top