|
View previous topic :: View next topic
|
| Author |
Message |
cmsmoon
New User
Joined: 17 Jun 2010
Posts: 75
Location: Chennai
|
|
|
|
Hi Friends,
Please help me to form a query.
How to get a past five dates value:
EX: Current date : 03AUG11
I results would be the following format
2011-08-02
2011-08-01
2011-07-31
2011-07-30
2011-07-29
is there any possible to convert this format ?
02AUG11
01AUG11
31JUL11
30JUL11
29JUL11 |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
v9 :
| Code: |
select varchar_format(current timestamp - 1 day,'DDMONYY') from sysibm.sysdummy1 union all
select varchar_format(current timestamp - 2 day,'DDMONYY') from sysibm.sysdummy1 union all
select varchar_format(current timestamp - 3 day,'DDMONYY') from sysibm.sysdummy1 union all
select varchar_format(current timestamp - 4 day,'DDMONYY') from sysibm.sysdummy1 union all
select varchar_format(current timestamp - 5 day,'DDMONYY') from sysibm.sysdummy1 |
|
|
| Back to top |
|
|
cmsmoon
New User
Joined: 17 Jun 2010
Posts: 75
Location: Chennai
|
|
|
|
| GuyC wrote: |
v9 :
| Code: |
select varchar_format(current timestamp - 1 day,'DDMONYY') from sysibm.sysdummy1 union all
select varchar_format(current timestamp - 2 day,'DDMONYY') from sysibm.sysdummy1 union all
select varchar_format(current timestamp - 3 day,'DDMONYY') from sysibm.sysdummy1 union all
select varchar_format(current timestamp - 4 day,'DDMONYY') from sysibm.sysdummy1 union all
select varchar_format(current timestamp - 5 day,'DDMONYY') from sysibm.sysdummy1 |
|
Thanks for your reply
I tried by it seems some error
SQLCODE = -440, ERROR: NO FUNCTION BY THE NAME VARCHAR_FORAMT HAVING
COMPATIBLE ARGUMENTS WAS FOUND IN THE CURRENT PATH
SQLSTATE = 42884 SQLSTATE RETURN CODE
SQLERRP = DSNXORFN SQL PROCEDURE DETECTING ERROR
SQLERRD = -100 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
SQLERRD = X'FFFFFF9C' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| GuyC wrote: |
varchar_format |
| cmsmoon wrote: |
SQLCODE = -440, ERROR: NO FUNCTION BY THE NAME VARCHAR_FORAMT HAVING
|
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| RTFM sometimes stands for "read the f*ing MESSAGE" |
|
| Back to top |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
|
|
| GuyC wrote: |
| RTFM sometimes stands for "read the f*ing MESSAGE" |
or : "Rolling on the floor m*******ting."
or : "Release The Flying Monkeys!"
or : "Repeat the first message" |
|
| Back to top |
|
|
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
| Code: |
WITH TEMP1 (DATE1) AS
(
select CURRENT_DATE - 1 day from sysibm.sysdummy1 union all
select CURRENT_DATE - 2 day from sysibm.sysdummy1 union all
select CURRENT_DATE - 3 day from sysibm.sysdummy1 union all
select CURRENT_DATE - 4 day from sysibm.sysdummy1 union all
select CURRENT_DATE - 5 day from sysibm.sysdummy1
)
SELECT SUBSTR(CHAR(DATE1),9,2)||
CASE MONTH(DATE1)
WHEN 01 THEN CHAR('JAN')
WHEN 02 THEN CHAR('FEB')
WHEN 03 THEN CHAR('MAR')
WHEN 04 THEN CHAR('APR')
WHEN 05 THEN CHAR('MAY')
WHEN 06 THEN CHAR('JUN')
WHEN 07 THEN CHAR('JUL')
WHEN 08 THEN CHAR('AUG')
WHEN 09 THEN CHAR('SEP')
WHEN 10 THEN CHAR('OCT')
WHEN 11 THEN CHAR('NOV')
WHEN 12 THEN CHAR('DEC')
END ||
SUBSTR(CHAR(YEAR(DATE1)),3,2) FROM TEMP1 WITH UR;
|
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
at least use
substr('xxJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC',month(date1)*3,3)
For something like that |
|
| Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
Thanks GuyC....
If will certainly reduce the complexity and number of lines.
| Code: |
WITH TEMP1 (DATE1) AS
(
select CURRENT_DATE - 1 day from sysibm.sysdummy1 union all
select CURRENT_DATE - 2 day from sysibm.sysdummy1 union all
select CURRENT_DATE - 3 day from sysibm.sysdummy1 union all
select CURRENT_DATE - 4 day from sysibm.sysdummy1 union all
select CURRENT_DATE - 5 day from sysibm.sysdummy1
)
SELECT SUBSTR(CHAR(DATE1),9,2)||
substr('xxJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC',month(DATE1)*3,3) ||
SUBSTR(CHAR(YEAR(DATE1)),3,2) FROM TEMP1 WITH UR;
|
|
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Below can also be used, if number days has to be increased.
| Code: |
WITH FASTFIVE(DATEE,LVL) AS
(
SELECT CURRENT DATE - 1 day,0 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT CURRENT DATE - (PARENT.LVL) DAY, PARENT.LVL+1
FROM FASTFIVE PARENT, SYSIBM.SYSDUMMY1 A
WHERE PARENT.LVL+1<=5
)
SELECT DATEE FROM FASTFIVE |
Thanks,
Sushanth |
|
| Back to top |
|
|
|
|
