Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Problem with current date in SQL- DB2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Problem with current date in SQL- DB2
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: 1072
Location: Oostende, Belgium

PostPosted: Fri Dec 05, 2014 2:14 am    Post subject:
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    Post subject: Reply to: Problem with current date in SQL- DB2
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    Post subject: Reply to: Problem with current date in SQL- DB2
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: 1544
Location: Andromeda Galaxy

PostPosted: Fri Dec 05, 2014 5:24 pm    Post subject:
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    Post subject: Reply to: Problem with current date in SQL- DB2
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Fetching data from JHS as per the cur... arunsoods All Other Mainframe Topics 4 Wed Nov 22, 2017 1:54 pm
No new posts Date in where clause - Windows Karthikeyan Subbarayan DB2 9 Wed Nov 15, 2017 9:07 pm
No new posts Having a problem FTP'ng to the MF pahiker All Other Mainframe Topics 2 Fri Nov 10, 2017 8:20 pm
No new posts Comparision with current time arunsoods DFSORT/ICETOOL 5 Thu Nov 09, 2017 10:37 am
This topic is locked: you cannot edit posts or make replies. Comparing current time with the time ... arunsoods SYNCSORT 1 Mon Oct 30, 2017 4:07 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us