How do I generate based on conditions

Instead of the number of days, you can use the number of months:

add_months(sysdate, -96)

Or, you can use an interval

systimestamp - interval '8 year'

one of my colleagues suggests using in between so and so date i.e, from Date1 to Date2

any thoughts on that... experts...

There are many ways to skin this cat.

Since this is just to obtain sample test data, is one any better than the others?

I just need one concrete way.... need to tackle the animal soon with the experts help.... :)

What parts do you still need help with?

You have examples of how to manipulate the current date (sysdate).

The between syntax is straight forward:

Select ...
Where date_col between sysdate-1 and sysdate;

Just use one of the date methods to get your ranges.

This is what I would use:

1. where DATE_END < add_months(sysdate, -96)
2. where DATE_END > add_months(sysdate, -96) and DATE_END < sysdate
3. where DATE_END = to_date('12/31/9999', 'mm/dd/yyyy')

No need for a between on 1 and 3.  If you really wanted to use between on 2, then it would be:


where DATE_END between add_months(sysdate, -96) and sysdate

where DATE_END = to_date('12/31/9999', 'mm/dd/yyyy')... I am facing an error...

THE DATE_END FIELD WITH A QUERY

WHERE DATE_END IS NULL RETURNS A LOT OF RECORDS

BUT NONE WHEN I SAY

WHERE DATE_END = to_date('12/31/9999', NULL)

REMEMBER I WANT TO REPLACE IN THE OUTCOME NULL WITH 12/31/9999

>>to_date('12/31/9999', NULL)

This results as a NULL and nothing can '=' null in oracle.

>>I am facing an error...

What is the error?

WHERE DATE_END IS NULL RETURNS A LOT OF RECORDS

BUT NONE WHEN I SAY >> MEANING ALL RECORDS WITH NULL DISPLAYED BUT NONE REPLACED WITH 12/31/9999 AS THE REQUIREMENT SAYS, WHEN I RUN THE QUERY  :(

WHERE DATE_END = to_date('12/31/9999', NULL)


PLEASE HELP....

>>WHERE DATE_END = to_date('12/31/9999', NULL)

I told you why this returns no rows.

 to_date('12/31/9999', NULL) results in NULL:

select  to_date('12/31/9999', NULL) from dual;

This makes that statement actually read:
WHERE DATE_END = NULL

In Oracle NOTHING can "=" null.

>>AS THE REQUIREMENT SAYS

Do you mean that the DATE_END is supposed to contain '12/31/9999'?

Yes

DATE_END SUPPOSED TO CONTAIN '12/31/9999' WHERE IT EQUALS NULL

Then I would go with:

nvl(DATE_END, to_date('12/31/9999', 'mm/dd/yyyy')) = to_date('12/31/9999', 'mm/dd/yyyy')

That way if any records actually contain the date, you would pick them up.  Because you are looking for nulls, you are going to do a full table scan anyway.

SELECT COL1, DATE_END
        FROM TABLE1
        WHERE COL1 IN('1','2','3','4','5','6','7','8','9','10','11','12')
    ORDER BY 1;

is the query. and when executed it gives me

COL1                               DATE_END
----------------                   ------------------------

1                                    
2
3
4
5
6
7
8
9
10
11
12


because in the table the records 1...12 have their date_end as null

and the query should be modified in a way that the output is

COL1                               DATE_END
----------------                   ------------------------

1                                     12/31/9999                                  
2                                     12/31/9999
3                                     12/31/9999
4                                     12/31/9999
5                                     12/31/9999
6                                     12/31/9999
7                                     12/31/9999
8                                     12/31/9999
9                                     12/31/9999
10                                   12/31/9999
11                                   12/31/9999
12                                   12/31/9999

 I hope I am clear this time, as to what I was trying out

Please help me experts... Thanks in advance

slighwv thanks for the post

Thinking it works I see the output as below

COL1                     DATE_END
000000001        12/31/9999 00:00:00
001000003        12/31/9999 00:00:00
004141413        12/31/9999 00:00:00
074747472        12/31/9999 00:00:00


1) The col1 records are all having date_end as null and so expecting every value under date_end to be 12/31/9999 is normal, but showing something different is abnormal like 12/31/9999 00:00:00

I would not like to display  00:00:00 after the date how to avoid ??

I think we are almost close to the solution here....

Thanks Expert.........

>>I would not like to display  00:00:00 after the date how to avoid ??

Does non-null date_end have a time portion that you might want displayed?

non-null date_end does not have a time portion displayed

nor do I want the time portion to be displayed...

Would like to thank all experts for especially their valuable time and their expert knowledge

Glad to help.