To Compare 2 dates

[radhika7983]

Hi,
I have 2 dates. One is migration date which will alwyas have a fixed value.Migration date is passed as job parameter. The other date is coming from source and will have different values. I need to compare 2 dates and if the difference between the 2 dates in greater than 3 working days, I need to reject that record. Here the problem is only as working days. So I have to exclude saturday and sunday.Can I have inputs as how to go about it?

[nikhilanshuman]

Hi,
I have 2 dates. One is migration date which will alwyas have a fixed value.Migration date is passed as job parameter. The other date is coming from source and will have different values. I need to compare 2 dates and if the difference between the 2 dates in greater than 3 working days, I need to reject that record. Here the problem is only as working days. So I have to exclude saturday and sunday.Can I have inputs as how to go about it?

are you trying to say that if the Other date is Satuday or Sunday then you have to ignore that date/record??[/b]

[radhika7983]

Hi,
I have 2 dates. One is migration date which will alwyas have a fixed value.Migration date is passed as job parameter. The other date is coming from source and will have different values. I need to compare 2 dates and if the difference between the 2 dates in greater than 3 working days, I need to reject that record. Here the problem is only as working days. So I have to exclude saturday and sunday.Can I have inputs as how to go about it?

are you trying to say that if the Other date is Satuday or Sunday then you have to ignore that date/record??[/b]

No..I have date A and date B..where A is migartion date and is fixed date and B is other chnaging date from source, after subtarcting these 2 dates I get a difference of 3 days, thn I should reject that record. But these 3 days of difference should only be working days that is excluding saturday and sunday.So in case the difference is 4 days, but 2 days in these 4 days is staurday and sunday thn I should not reject this record even the difference is greater than 3 days.

[ray.wurlod]

Do a (range?) lookup against a time dimension table, with multiple row return, constrained on weekday name not being 'Sunday' or 'Saturday' to get a list of dates.

[ArndW]

The DaysSinceFromDate() function gives you the difference in days between your two dates. The WeekdayFromDate() function returns the weekday number. Then use the days difference and day-of-week to compute valid dates.

[gssr]

No..I have date A and date B..where A is migartion date and is fixed date and B is other chnaging date from source, after subtarcting these 2 dates I get a difference of 3 days, thn I should reject that record. But these 3 days of difference should only be working days that is excluding saturday and sunday.So in case the difference is 4 days, but 2 days in these 4 days is staurday and sunday thn I should not reject this record even the difference is greater than 3 days.

As one of the date is fixed there is no need to subract.
Consider 8th Apr is your fixed date
Then you should get the records of having date
5 to 13th of Apr!!!

Put a constrint to fetch this set of records!!!

[gssr]

No..I have date A and date B..where A is migartion date and is fixed date and B is other chnaging date from source, after subtarcting these 2 dates I get a difference of 3 days, thn I should reject that record. But these 3 days of difference should only be working days that is excluding saturday and sunday.So in case the difference is 4 days, but 2 days in these 4 days is staurday and sunday thn I should not reject this record even the difference is greater than 3 days.

As one of the date is fixed there is no need to subract.
Consider 8th Apr is your fixed date
Then you should get the records of having date
5 to 13th of Apr!!!

Put a constrint to fetch this set of records!!!

[MT]

Hi

well I think this comes down to two situations:
1) if the migartion date is a Monday or Tuesday then you will look for a difference of 3

2) if the migration date is Wednesday, Thursday or Friday then use a difference of 5

So you just have to determine the day (name or number) i.e. with oconv

regards
Michael

[radhika7983]

The DaysSinceFromDate() function gives you the difference in days between your two dates. The WeekdayFromDate() function returns the weekday number. Then use the days difference and day-of-week to co ...

I did not get your Full reply.

[nikhilanshuman]

Okay..So as per your requirement,you need to get all the dates lying between two dates.Then you need to determine days for all those dates.
Then,you have to adjust counters accordingly.

You will have to create a datastage job and call a stored procedure from the job for this.

Following are the steps to accomplish this :

a) Create a stored procedure.Parameters : => your Migration date .Say IN_DateVar
b) Read source table into a cursor.And iterate through all the records of
the table one by one.
c) For each record do --- compare the table's record's date with the fixed date IN_DateVar.Now,you have to find all the dates lying between these two dates.
Use the following link to get the dates between two dates.
http://asktom.oracle.com/pls/asktom/f?p ... 2643282111


d) For each dates now you have to find the day.Using to_date function you will get the DAY for a date.

e) Declare a variable in the beginning say counter.

e) For each day which is not SATURDAY and SUNDAY,increment the counter by 1(i.e. counter=counter+1)

f) Once ,the loop is done for all dates between fixed and source date.Compare the couter with three.

h) If the value of counter is three or more than three,populate that record in the reject table.

i) If the counter is less than three,populate it in the main table.

The above steps need to be repeated for each record.


Hope this helps....

[ray.wurlod]

What evidence do you have that Oracle is involved?

Funnily enough, not everyone in the world uses Oracle.

[radhika7983]

Hi

well I think this comes down to two situations:
1) if the migartion date is a Monday or Tuesday then you will look for a difference of 3

2) if the migration date is Wednesday, Thursday or Friday then use a difference of 5

So you just have to determine the day (name or number) i.e. with oconv

regards
Michael

You mean to say that If my migration date is monday or tuesday than I just need to check for difference of 3 bwteen the 2 dates? If yes, Than I dont think this will work.Suppose my migration date is monday and the other date day is wednesday. In this case the difference is 4 days, but I cannot reject the record because out of days, 2 are saturday and sunday. This record should still pass to output

[radhika7983]

What evidence do you have that Oracle is involved?

Funnily enough, not everyone in the world uses Oracle.

Correct!! This is complete file system and there si no data base involved here.

[nikhilanshuman]

What evidence do you have that Oracle is involved?

Funnily enough, not everyone in the world uses Oracle.

And what is the evidence given that any file is being used?In fact..the source is not mentioned in the question itself...

You should try putting solution to the problems..rather than making fun of the people who are trying to help....

[MT]

Hi

well I think this comes down to two situations:
1) if the migartion date is a Monday or Tuesday then you will look for a difference of 3

2) if the migration date is Wednesday, Thursday or Friday then use a difference of 5

So you just have to determine the day (name or number) i.e. with oconv

regards
Michael

You mean to say that If my migration date is monday or tuesday than I just need to check for difference of 3 bwteen the 2 dates? If yes, Than I dont think this will work.Suppose my migration date is monday and the other date day is wednesday. In this case the difference is 4 days, but I cannot reject the record because out of days, 2 are saturday and sunday. This record should still pass to output

Hi,

well I do not understand this - sorry..
Let us assume:
Your (fix) migration date is monday.
When other date is (let us say wednesday of the same week) the difference of the two dates has to be checked if it is > 3 then reject it.

In my calender there is no saturday or sunday between monday and wednesday....

kind regards
Michael