Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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

Active Member


Joined: 07 Feb 2009
Posts: 984
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: 1529
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: 1278
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 Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
This topic is locked: you cannot edit posts or make replies. Get correct date and time when curren... balaji81_k DB2 24 Fri Oct 14, 2016 10:40 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us