View previous topic :: View next topic
Author
Message
swapnil781 New User Joined: 29 Jul 2009Posts: 10 Location: Bangalore
Hi,
I am facing issues with CURRENT DATE function in DB2.
Requirement : I have a table which has 4 columns CUST_NO, CITY, INTF_DT, FLAG.
Code:
CUST_NO CITY INTF_DT FLAG
1, A, 20141029, X
2, B, 20141026, Y
3, C, 20140603, X
4, D, 20140501, Y
5, E, 20140401, X
6, F, 20140201, Y
My requirement is to select the CUST_NO, FLAG for less than 2 months from the CURRENT DATE.
So output should have only CUST_NO = A & B, FLAG = X & X
Below is the query:
Code:
SELECT CUST_NO, FLAG
FROM TAB1
WHERE INTF_DT = CURRENT DATE - 2 MONTHS
But as the CURRENT DATE format is 2014-12-01 and INTF_DT format is CCYYMMDD, I am not getting the expected results.
I even used Substring function as below.
Code:
SELECT CUST_NO, FLAG, SUBSTR(CHAR(CURRENT DATE,ISO),1,4) || SUBSTR(CHAR(CURRENT DATE,ISO),6,2) || SUBSTR(CHAR(CURRENT DATE,ISO),9,2)
FROM TAB 1
In this case I am getting Current date format as 20141201 but I am not able to substract the date by 2 months.
I even tried
Code:
SELECT CUST_NO, FLAG
FROM TAB1
WHERE INTF_DT = (SUBSTR(CHAR(CURRENT DATE,ISO),1,4) || SUBSTR(CHAR(CURRENT DATE,ISO),6,2) || SUBSTR(CHAR(CURRENT DATE,ISO),9,2)) - 2 MONTHS )
but getting -180 SQL Code.
appreciate your helpful inputs.
Code'd
Back to top
prino Senior Member Joined: 07 Feb 2009Posts: 1306 Location: Vilnius, Lithuania
It's not going to help you, but which imbecile PHB decided to forgo the DB2 builtin DATE datatype for character data?
Back to top
swapnil781 New User Joined: 29 Jul 2009Posts: 10 Location: Bangalore
It is been already existing in my system
Back to top
swapnil781 New User Joined: 29 Jul 2009Posts: 10 Location: Bangalore
Any one there guys to help me with the above issue ?
Back to top
Pandora-Box Global Moderator Joined: 07 Sep 2006Posts: 1592 Location: Andromeda Galaxy
You need to try to debug the issue yourself before posting in forum
Did you try to understand what -180 mean??
-180 Mean the DATE and TIME format is not correct or contains spaces
Hint: You need to first get CURRENT DATE - 2 MONTHS and then SUBSTR'ng it
Back to top
swapnil781 New User Joined: 29 Jul 2009Posts: 10 Location: Bangalore
I resolved the query by myself.. below is the solution
SELECT CUST_NO, FLAG
WHERE INTF_DT >= SUBSTR(CHAR(CURRENT DATE - 2 MONTHS,ISO),1,4) || SUBSTR(CHAR(CURRENT DATE - 2 MONTHS,ISO),6,2) || SUBSTR(CHAR(CURRENT DATE - 2 MONTHS,ISO),9,2)
Thanks every one for their reply...
Back to top
GuyC Senior Member Joined: 11 Aug 2009Posts: 1281 Location: Belgium
or
Code:
WHERE INTF_DT > VARCHAR_FORMAT(current_timestamp - 2 months,'YYYYMMDD')
Back to top
Please enable JavaScript!