IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Problem with current date in SQL- DB2
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
swapnil781

New User


Joined: 29 Jul 2009
Posts: 10
Location: Bangalore
PostPosted: Fri Dec 05, 2014 1:59 am
 Reply with quote
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
View user's profile Send private message
prino

Senior Member


Joined: 07 Feb 2009
Posts: 1306
Location: Vilnius, Lithuania
PostPosted: Fri Dec 05, 2014 2:14 am
 Reply with quote
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
View user's profile Send private message
swapnil781

New User


Joined: 29 Jul 2009
Posts: 10
Location: Bangalore
PostPosted: Fri Dec 05, 2014 2:20 am
 Reply with quote
It is been already existing in my system
Back to top
View user's profile Send private message
swapnil781

New User


Joined: 29 Jul 2009
Posts: 10
Location: Bangalore
PostPosted: Fri Dec 05, 2014 3:08 pm
 Reply with quote
Any one there guys to help me with the above issue ?
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
PostPosted: Fri Dec 05, 2014 5:24 pm
 Reply with quote
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
View user's profile Send private message
swapnil781

New User


Joined: 29 Jul 2009
Posts: 10
Location: Bangalore
PostPosted: Fri Dec 05, 2014 5:32 pm
 Reply with quote
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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
PostPosted: Fri Jan 23, 2015 3:49 pm
 Reply with quote
or
Code:
WHERE INTF_DT > VARCHAR_FORMAT(current_timestamp - 2 months,'YYYYMMDD')
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 

Similar Topics
Topic Forum Replies
No new posts Replacing 'YYMMDD' with date, varying... SYNCSORT 3
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Map Vols and Problem Dataset All Other Mainframe Topics 2
No new posts To get the the current time DFSORT/ICETOOL 13
No new posts Need to convert date format DFSORT/ICETOOL 20
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us