|
View previous topic :: View next topic
|
| Author |
Message |
Shripad AVhad
New User
Joined: 02 Sep 2015
Posts: 2
Location: India
|
|
|
|
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 |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
| Back to top |
|
|
Shripad AVhad
New User
Joined: 02 Sep 2015
Posts: 2
Location: India
|
|
|
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| 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 |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
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 |
|
|
Lori A Darling
New User
Joined: 18 Aug 2015
Posts: 2
Location: USA
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Lori A Darling
New User
Joined: 18 Aug 2015
Posts: 2
Location: USA
|
|
|
|
| I will have to catch up to de-mangling. Thank you for the help! |
|
| Back to top |
|
|
|
|
