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
 

 

given a date how to determine the first day of the quarter

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sunilmfs

New User


Joined: 09 Nov 2010
Posts: 20
Location: hyderabad

PostPosted: Mon Jun 27, 2011 12:32 pm    Post subject: given a date how to determine the first day of the quarter
Reply with quote

when I enter the date or current date, I need the first day of that quarter..

Plz help
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Jun 27, 2011 12:51 pm    Post subject: Reply to: query
Reply with quote

when posting it would be nice to use a meaningful title!
topic edited
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7226

PostPosted: Mon Jun 27, 2011 2:12 pm    Post subject: Reply to: given a date how to determine the first day of the
Reply with quote

Store the quarter ddmm's somewhere.

Compare your ddmm to the stored ones, using >, <, or whatever. The one that fits is the first day of the quarter for the date you have.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Jun 27, 2011 2:14 pm    Post subject:
Reply with quote

Code:

WITH WORKTAB(WRKDATE)
  AS(
     SELECT DATE(SUBSTR(CHAR(YEAR(CURRENT_DATE)),1,4)
                 ||SUBSTR('-01',1,3)||SUBSTR('-01',1,3)
                )
       FROM SYSIBM.SYSDUMMY1
     UNION ALL
     SELECT DATE(SUBSTR(CHAR(YEAR(CURRENT_DATE)),1,4)
            ||SUBSTR('-04',1,3)||SUBSTR('-01',1,3)
                )
       FROM SYSIBM.SYSDUMMY1
     UNION ALL
     SELECT DATE(SUBSTR(CHAR(YEAR(CURRENT_DATE)),1,4)
            ||SUBSTR('-07',1,3)||SUBSTR('-01',1,3)
                )
       FROM SYSIBM.SYSDUMMY1
     UNION ALL
     SELECT DATE(SUBSTR(CHAR(YEAR(CURRENT_DATE)),1,4)
            ||SUBSTR('-10',1,3)||SUBSTR('-01',1,3)
                )
       FROM SYSIBM.SYSDUMMY1
    )
SELECT WRKDATE
  FROM WORKTAB
 WHERE QUARTER(WRKDATE) = QUARTER(CURRENT_TIMESTAMP)
;


substitute your host variable for QUARTER(CURRENT_TIMESTAMP)
Back to top
View user's profile Send private message
sunilmfs

New User


Joined: 09 Nov 2010
Posts: 20
Location: hyderabad

PostPosted: Mon Jun 27, 2011 2:27 pm    Post subject: Reply to: given a date how to determine the first day of the
Reply with quote

Thanks Dick
enrico:from next time onwards i will do properly
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Mon Jun 27, 2011 2:33 pm    Post subject:
Reply with quote

Alternatively... without using with clause

Code:


SELECT
CASE
    WHEN QUARTER(CURRENT DATE) = 1 THEN DATE(STRIP(CHAR(YEAR(CURRENT DATE)))||CHAR('-01-01'))
    WHEN QUARTER(CURRENT DATE) = 2 THEN DATE(STRIP(CHAR(YEAR(CURRENT DATE)))||CHAR('-04-01'))
    WHEN QUARTER(CURRENT DATE) = 3 THEN DATE(STRIP(CHAR(YEAR(CURRENT DATE)))||CHAR('-07-01'))
    WHEN QUARTER(CURRENT DATE) = 4 THEN DATE(STRIP(CHAR(YEAR(CURRENT DATE)))||CHAR('-10-01'))
END
  FROM SYSIBM.SYSDUMMY1
;

Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Jun 27, 2011 2:42 pm    Post subject:
Reply with quote

Code:
datecol - (day(datecol) -1) days - mod( month(datecol) - 1,3) months

which is not the same as
Code:
datecol - mod( month(datecol) - 1,3) months - (day(datecol) -1) days
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Jun 27, 2011 2:52 pm    Post subject: Reply to: given a date how to determine the first day of the
Reply with quote

OK i' ll bite ...
what about something like
Code:
    ...
     SELECT DATE(CHAR(YEAR(<the date>)),1,4) ||
                 CHAR('-0') ||
                 CHAR(QUARTER(<the date>)) ||
                 CHAR('-01')
                )
     FROM <the appropriate table>
    ...


just the logic, the syntax might stink icon_biggrin.gif
feel free to bash me if I am blatantly wrong

(*) I usually have strong preferences for algorithmic solutions

quarter = ( month - 1 ) % 4 + 1
where % is the integer division
or if the integer division operator is not available
quarter = INT( ( month - 1 ) / 4 ) + 1
where / is the standard division and INT returns the integer part
Back to top
View user's profile Send private message
sunilmfs

New User


Joined: 09 Nov 2010
Posts: 20
Location: hyderabad

PostPosted: Mon Jun 27, 2011 2:52 pm    Post subject: Reply to: given a date how to determine the first day of the
Reply with quote

bharat getting error

Command ===> Scroll ===> CSR
SELECT 00670078
CASE 00680078
WHEN QUARTER(CURRENT DATE) = 1 THEN 00690078
DATE(STRIP(CHAR(YEAR(CURRENT DATE)))||CHAR('-01-01')) 00700078
WHEN QUARTER(CURRENT DATE) = 2 THEN 00710078
DATE(STRIP(CHAR(YEAR(CURRENT DATE)))||CHAR('-04-01')) 00720078
WHEN QUARTER(CURRENT DATE) = 3 THEN 00730078
DATE(STRIP(CHAR(YEAR(CURRENT DATE)))||CHAR('-07-01')) 00740078
WHEN QUARTER(CURRENT DATE) = 4 THEN 00750078
DATE(STRIP(CHAR(YEAR(CURRENT DATE)))||CHAR('-10-01')) 00760078
END 00770078
FROM SYSIBM.SYSDUMMY1 00780078
; 00790078
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD CASE. TOKEN
<END-OF-STATEMENT> QUERYNO HAVING WHERE GROUP ORDER INTERSECT WAS
EXPECTED
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Jun 27, 2011 3:22 pm    Post subject:
Reply with quote

GuyC
what you say is true, but I don't understand the relevance to this thread.

gylbharat,
your sql is much better than mine.

Sunil,

gylbharat's sql works for me.

yours however, requires some editing.
remove the pg/ln numbers.

and learn to use BB code when you post.
otherwise your select works.

Code:

      SELECT                                                            00670078
      CASE                                                              00680078
          WHEN QUARTER(CURRENT DATE) = 1 THEN                           00690078
      DATE(STRIP(CHAR(YEAR(CURRENT DATE)))||CHAR('-01-01'))             00700078
          WHEN QUARTER(CURRENT DATE) = 2 THEN                           00710078
      DATE(STRIP(CHAR(YEAR(CURRENT DATE)))||CHAR('-04-01'))             00720078
          WHEN QUARTER(CURRENT DATE) = 3 THEN                           00730078
      DATE(STRIP(CHAR(YEAR(CURRENT DATE)))||CHAR('-07-01'))             00740078
          WHEN QUARTER(CURRENT DATE) = 4 THEN                           00750078
      DATE(STRIP(CHAR(YEAR(CURRENT DATE)))||CHAR('-10-01'))             00760078
      END                                                               00770078
        FROM SYSIBM.SYSDUMMY1                                           00780078
      ;                                                                 00790078
Back to top
View user's profile Send private message
sunilmfs

New User


Joined: 09 Nov 2010
Posts: 20
Location: hyderabad

PostPosted: Mon Jun 27, 2011 4:09 pm    Post subject: Reply to: given a date how to determine the first day of the
Reply with quote

Hi Dick...
Please let me know about BB code..
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Jun 27, 2011 4:12 pm    Post subject:
Reply with quote

Quote:
Please let me know about BB code..

why not read about it Yourself here ?
http://www.ibmmainframes.com/faq.php
Back to top
View user's profile Send private message
sunilmfs

New User


Joined: 09 Nov 2010
Posts: 20
Location: hyderabad

PostPosted: Mon Jun 27, 2011 4:41 pm    Post subject: Reply to: given a date how to determine the first day of the
Reply with quote

enrico: got it..

Bharat: Query is working fine... Thanks
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Jun 27, 2011 7:07 pm    Post subject:
Reply with quote

dbzTHEdinosauer wrote:
GuyC
what you say is true, but I don't understand the relevance to this thread.

datecol - (day(datecol) -1) days - mod( month(datecol) - 1,3) monthswill return the first day of the quarter for any given datecol, just like the other solutions in this thread

enrico :
2011-02-01 is not the starting date of the second quarter
2011-03-01 is not the starting date of the third quarter
2011-04-01 is not the starting date of the fourth quarter
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Jun 28, 2011 1:21 pm    Post subject:
Reply with quote

for my version of db2, I had to modify the sql from:

Code:
datecol - (day(datecol) -1) days - mod( month(datecol) - 1,3) months


to: (had to CAST the 3 in the MOD function to an integer)
Code:

CURRENT DATE - (day(CURRENT DATE) -1) days - mod( month(CURRENT DATE) - 1,int(3)) months


actually, when using a literal or host-variable, had to add additional CAST functions:
Code:

DATE('2011-01-01') - (DAY(DATE('2011-01-01')) -1) DAYS - MOD( MONTH(DATE('2011-01-01')) - 1,INT(3)) MONTHS


Still,
works great,
and is the better of the 3 sqls.

Thx GuyC
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Tue Jun 28, 2011 1:48 pm    Post subject: Reply to: given a date how to determine the first day of the
Reply with quote

Quote:
enrico :
2011-02-01 is not the starting date of the second quarter
2011-03-01 is not the starting date of the third quarter
2011-04-01 is not the starting date of the fourth quarter

icon_redface.gif a despicable brain fart !

so to keep the terminology straight I should have written also

instead of
Quote:
quarter = ( month - 1 ) % 4 + 1
where % is the integer division
or if the integer division operator is not available
quarter = INT( ( month - 1 ) / 4 ) + 1
where / is the standard division and INT returns the integer part


Quote:
firstmonthofquarter = ( month - 1 ) % 4 + 1
where % is the integer division
or if the integer division operator is not available
firstmonthofquarter = INT( ( month - 1 ) / 4 ) + 1
where / is the standard division and INT returns the integer part
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
No new posts TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
This topic is locked: you cannot edit posts or make replies. Get correct date and time when curren... balaji81_k DB2 24 Fri Oct 14, 2016 10:40 pm
No new posts Change date (DD/MM/YY) in 2nd record ... uday kiran DFSORT/ICETOOL 12 Wed Sep 07, 2016 10:57 pm
No new posts Add system date inside of a PS file a... pramitdas DFSORT/ICETOOL 5 Sun Aug 28, 2016 12:06 am


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