| View previous topic :: View next topic |
| Author |
Message |
yadav2005
Intermediate
Joined: 10 Jan 2005
Posts: 348
Topics: 144
|
 Posted: Mon May 14, 2012 10:19 pm Post subject: Need help in dates conversion for a query |
 |
|
Hi,
I have a DB2 Table with some columns and some columns are as below | Code: | Select Column Name Col No Col Type Length Scale Null Def FP
* * * * * * * *
------ ------------------ ------ -------- ------ ------ ---- --- --
.
.
.
SORT_DATE 13 DECIMAL 7 0 N Y N
SORT_QTY 18 DECIMAL 1 0 N Y N |
I have a query as below
| Code: | SELECT
A,
B,
SUM(SORT_QTY),
MAX(SORT_DATE)
FROM TABLE
WHERE
C = '000'
AND SORT_DATE > 1111231 AND SORT_DATE < 1120401
GROUP BY SOME COLUMNS ALONG WITH SORT_DATE; |
I want to change this query to | Code: | SELECT
A,
B,
SUM(SORT_QTY),
MAX(SORT_DATE)
FROM TABLE
WHERE
C = '000'
AND SORT_DATE > 1(CURRENT DATE - 3 MONTHS ) AND SORT_DATE < 1(CURRENT DATE)
GROUP BY SOME COLUMNS ALONG WITH SORT_DATE;
1 should be always added in the first byte in the comparision
111321 this should be currrent date - 3 Months in YYMMDD format
120401 this should be the current date in YYMMDD format
|
Can anyone help me on this ? |
|
| Back to top |
|
 |
GuyC
Supermod
Joined: 27 May 2011
Posts: 35
Topics: 0
|
| Posted: Tue May 15, 2012 6:10 am Post subject: |
|
|
how about :
(year(CURRENT DATE )-1900) *10000 + month(CURRENT DATE ) * 100 + day(CURRENT DATE)
or
integer(varchar_format(current_timestamp,'IYYYMMDD')) - 19000000 |
|
| Back to top |
|
|
yadav2005
Intermediate
Joined: 10 Jan 2005
Posts: 348
Topics: 144
|
| Posted: Tue May 15, 2012 7:14 am Post subject: |
|
|
GuyC,
Thanks for the reply but I am not clear what exactly you are trying to do.Plus I am in DB2 V9 CM Mode so cannot use New Function mode.I did try using both the formats and
I get the below error.
| Code: |
DSNT408I SQLCODE = -4700, ERROR: ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE
DSNT418I SQLSTATE = 56038 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXORFN SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -666 0 0 -1 982 802 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFD66' X'00000000' X'00000000' X'FFFFFFFF' X'0000
INFORMATION
|
|
|
| Back to top |
|
|
GuyC
Supermod
Joined: 27 May 2011
Posts: 35
Topics: 0
|
| Posted: Tue May 15, 2012 7:20 am Post subject: |
|
|
i think year(), month() and day() should work no matter which DB2 version you are on.
Varchar_format() is indeed a DB2 9 nfm function.
What I am trying to do is :
year(current date) => 2012
year(current date) - 1900 => 112
(year(current date) - 1900)* 10000 = 1120000
month(current date) * 100 => 0500
day(current date) => 15
1120000+ 0500 + 15 = 1120515 which is the number you are looking for. |
|
| Back to top |
|
|
yadav2005
Intermediate
Joined: 10 Jan 2005
Posts: 348
Topics: 144
|
| Posted: Tue May 15, 2012 8:24 am Post subject: |
|
|
| Thanks so much .I understand now and it helped me and I was able to get my results. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
