Need to compare two dates with different format in a Query

rupesh gullu · New User Joined: 12 Dec 2008 Posts: 96 Location: Gurgaon

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

guptae · Posted: Mon Dec 07, 2009 1:42 pm

Hello Rupesh,

Could you please provide table defination of these two fields?

rupesh gullu · New User Joined: 12 Dec 2008 Posts: 96 Location: Gurgaon

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

ashimer · Posted: Mon Dec 07, 2009 2:37 pm

You might have to change the format of one date

rupesh gullu · New User Joined: 12 Dec 2008 Posts: 96 Location: Gurgaon

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

ashimer · Posted: Mon Dec 07, 2009 2:45 pm

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 .

rupesh gullu · New User Joined: 12 Dec 2008 Posts: 96 Location: Gurgaon

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

dbzTHEdinosauer · Posted: Mon Dec 07, 2009 5:30 pm

SUBSTR(CHAR(TRM_DT),1,8) ??

ashimer · Posted: Mon Dec 07, 2009 5:58 pm