|
View previous topic :: View next topic
|
| Author |
Message |
sunilmfs
New User
Joined: 09 Nov 2010
Posts: 20
Location: hyderabad
|
|
|
|
when I enter the date or current date, I need the first day of that quarter..
Plz help |
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
when posting it would be nice to use a meaningful title!
topic edited |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| 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 |
|
|
sunilmfs
New User
Joined: 09 Nov 2010
Posts: 20
Location: hyderabad
|
|
|
|
Thanks Dick
enrico:from next time onwards i will do properly |
|
| Back to top |
|
|
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| 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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
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 
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 |
|
|
sunilmfs
New User
Joined: 09 Nov 2010
Posts: 20
Location: hyderabad
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
sunilmfs
New User
Joined: 09 Nov 2010
Posts: 20
Location: hyderabad
|
|
|
|
Hi Dick...
Please let me know about BB code.. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
| Back to top |
|
|
sunilmfs
New User
Joined: 09 Nov 2010
Posts: 20
Location: hyderabad
|
|
|
|
enrico: got it..
Bharat: Query is working fine... Thanks |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| 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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| 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 |
 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 |
|
|
|
|
