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
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
Warnings : 1

Senior Member


Joined: 07 Feb 2009
Posts: 1238
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

Moderator


Joined: 07 Sep 2006
Posts: 1565
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 Date conversion using SORT SYNCSORT 4
No new posts Sysncsort - Justification problem SYNCSORT 6
No new posts Date Conversion Y2T to Y2W SYNCSORT 8
No new posts Date format conversion DFSORT/ICETOOL 4
No new posts Hex Value - vb file replace old date ... DFSORT/ICETOOL 14
Search our Forums:

Back to Top