Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DATEADD query trouble

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: DATEADD query trouble
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Tue Sep 15, 2015 12:49 pm    Post subject: Reply to: DATEADD query trouble
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: 1278
Location: Belgium

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

db2 isn't SQLServer

http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_scalarfunctionsintro.dita

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    Post subject: Re: Reply to: DATEADD query trouble
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: 1738
Location: Bloomington, IL

PostPosted: Wed Sep 16, 2015 3:47 pm    Post subject: Re: Reply to: DATEADD query trouble
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: 1219
Location: Israel

PostPosted: Thu Sep 17, 2015 2:51 pm    Post subject:
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    Post subject: Easy routine to get last day of a month
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Thu Sep 17, 2015 6:39 pm    Post subject: Reply to: DATEADD query trouble
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    Post subject: Thanks!
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us