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
 

 

Need to compare two dates with different format in a Query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rupesh gullu

New User


Joined: 12 Dec 2008
Posts: 96
Location: Gurgaon

PostPosted: Mon Dec 07, 2009 1:34 pm    Post subject: Need to compare two dates with different format in a Query
Reply with quote

Hi,

I have one date in AACCUM_PRD_STRT_DT in format 2007-06-01
and OTHER one IS TRM_DT in format 20081001

This has to be given in this way
Code:


GIVEN A.ACCUM_PRD_STRT_DT <= B.TRM_DT AND



As the formats are different how can i achive this result. I need to use this Query in QMF

Regards,
Rupesh
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Mon Dec 07, 2009 1:42 pm    Post subject:
Reply with quote

Hello Rupesh,

Could you please provide table defination of these two fields?
Back to top
View user's profile Send private message
rupesh gullu

New User


Joined: 12 Dec 2008
Posts: 96
Location: Gurgaon

PostPosted: Mon Dec 07, 2009 2:24 pm    Post subject: Reply to: Need to compare two dates with different format in
Reply with quote

Guptae,

Both are char field AACCUM_PRD_STRT_DT is CCYY-MM-DD of 10 bytes each and other TRM_DT is Char 8 bytes. Also

Regards,
Rupesh
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Dec 07, 2009 2:37 pm    Post subject:
Reply with quote

You might have to change the format of one date


Code:

REPLACE(CHAR(DATE(A.ACCUM_PRD_STRT_DT),ISO),'-','') <= B.TRM_DT
Back to top
View user's profile Send private message
rupesh gullu

New User


Joined: 12 Dec 2008
Posts: 96
Location: Gurgaon

PostPosted: Mon Dec 07, 2009 2:42 pm    Post subject: Reply to: Need to compare two dates with different format in
Reply with quote

Ashimer,

So this Query will change '-' to spaces and will give result as 20090601?? .. One more thing my B.TRM_DT is not char its declaration is DEC(8,0).

Now how i will compare in a Query in which one is Char and other is Decimal

Regards,
Rupesh
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Dec 07, 2009 2:45 pm    Post subject:
Reply with quote

It wont replace it by spaces.it will remove the '-' character.
note : do not put spaces between the quotes.
Now DEC(8,0) is not a date field .. Use CHAR(col) to convert this to character .
Back to top
View user's profile Send private message
rupesh gullu

New User


Joined: 12 Dec 2008
Posts: 96
Location: Gurgaon

PostPosted: Mon Dec 07, 2009 5:15 pm    Post subject: Reply to: Need to compare two dates with different format in
Reply with quote

Ashimr,

thanks for help but one problem i am facing. When i am changing DEC to Char it is giving me one period at end and due to that it is not comparing with char value.

e.g CHAR(TRM_DT) it is giving one period at end

20090809. This period at end is giving problem Is there any way to solve it

Regards,
rupesh
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Dec 07, 2009 5:30 pm    Post subject:
Reply with quote

SUBSTR(CHAR(TRM_DT),1,8) ??
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Dec 07, 2009 5:58 pm    Post subject:
Reply with quote

Code:

DECIMAL(REPLACE(CHAR(DATE(A.ACCUM_PRD_STRT_DT),ISO),'-',''),10,0)
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
No new posts Storing huge volume of data, compare ... Pradeep K M All Other Mainframe Topics 3 Mon Jan 16, 2017 5:08 pm
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am


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