To Compare 2 dates
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 16
- Joined: Wed Apr 07, 2010 10:44 pm
To Compare 2 dates
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?
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?
Radhika Sharma
-
- Participant
- Posts: 58
- Joined: Tue Nov 17, 2009 3:38 am
Re: To Compare 2 dates
are you trying to say that if the Other date is Satuday or Sunday then you have to ignore that date/record??[/b]radhika7983 wrote: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?
Nikhil
-
- Participant
- Posts: 16
- Joined: Wed Apr 07, 2010 10:44 pm
Re: To Compare 2 dates
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.nikhilanshuman wrote:are you trying to say that if the Other date is Satuday or Sunday then you have to ignore that date/record??[/b]radhika7983 wrote: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?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Re: To Compare 2 dates
As one of the date is fixed there is no need to subract.radhika7983 wrote: 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.
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!!!
RAJ
Re: To Compare 2 dates
As one of the date is fixed there is no need to subract.radhika7983 wrote: 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.
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!!!
RAJ
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
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
-
- Participant
- Posts: 16
- Joined: Wed Apr 07, 2010 10:44 pm
-
- Participant
- Posts: 58
- Joined: Tue Nov 17, 2009 3:38 am
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....
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....
Nikhil
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 16
- Joined: Wed Apr 07, 2010 10:44 pm
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 outputMT wrote: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
-
- Participant
- Posts: 16
- Joined: Wed Apr 07, 2010 10:44 pm
-
- Participant
- Posts: 58
- Joined: Tue Nov 17, 2009 3:38 am
And what is the evidence given that any file is being used?In fact..the source is not mentioned in the question itself...ray.wurlod wrote:What evidence do you have that Oracle is involved?
Funnily enough, not everyone in the world uses Oracle.
You should try putting solution to the problems..rather than making fun of the people who are trying to help....
Nikhil
Hi,radhika7983 wrote: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 outputMT wrote: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
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