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 How to pick only YYMMDD from DATE1P (... atulbaviskar SYNCSORT 7 Wed Mar 22, 2017 11:39 am
No new posts Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am


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