Niyazi wrote:
Hi everyone,
I have a sql table that has 5 column as:
cl1Month - cl1_3Month - cl3_6Month - cl6_12Month - clMoreThan12Month
Now I have to date and I have to find the differences and check as month and
put into one of the above column.
If the diiferences is 0 or 30 days than value should be store in cl1Month.
Example:
If I run the program today (24th July 2006) I have to find due Date from DB.
And that say the due date is: 9th September 2006.
Then I have to get end of last month date from DB as 31 June 2006. This is
done and the date store in DB every end of the month from another program.
I'm not sure I understand you. It sounds like you want to determine
which category 24th September falls into, relative to 31st June - that
is, whether it is at most one month after, between 1 and 3 months after,
and so on. Is this correct?
By the way, you don't need to store the month-end date to know what it
is: For example, to find the month-end date of June 2006, we can just say
DateSerial(2006, 7, 0)
This returns the "0th" day of July - that is to say, the last day of
June. (Which is the 30th, not the 31st!)
Now I use:
dtDiff = DateDiff(DateInterval.Day, cdLastMonth, cdDueDate)
and I find 82 day.
I want to convert this integer (82) (if possible) into month.
And then I will asgin the myDecimal value in the right column.
As wecan see 82 days is nearly 2 months 20 day. But not exactly.
Becasue some month have 30 days and Feb has 28 or 29.
I want to consider all this and find a solution but unfortunatly I am not
able to achive it.
If finding day is 31 and I will have to count from last month date to see if
it is in 1 month range.
And if it is the MyDecimal value have to goto in cl1Month column.
This approach will not work. As you have already found out, once you
subtract a date from a date and get a number of days, you have lost all
*contextual* information, and it's that information that is needed to
count months.
It's essentially impossible to answer the question "How many months is
30 days?", because it depends *which* 30 days. Jan 1 to Jan 30 is just
under 1 month; Sep 1 to Sep 30 is exactly 1 month; and Feb 1 to Mar 2 is
a little over 1 month.
So what you need to do - and the answers you get should be checked with
whoever is for the *business rule* which applies - is proceed on a
month-by-month basis, iteratively.
- start with your START DATE in a variable
- in a loop:
- add one month to the current work variable (using AddMonths)
- loop until the work variable is later than the END DATE
- now we know how many months there are between them
For example, with your 30 June - 24 Sep example:
- start with 30 June
- add a month - we get 31st July
- this is not later than the end date
- add a month - we get 31st August
- this is not later than the end date
- add a month - we get 30th September
- this IS later than the end date, so we stop
We added 3 months to get past the end date, so we know the difference is
between 2 and 3 months.
--
Larry Lard
la*******@googlemail.com
The address is real, but unread - please reply to the group
For VB and C# questions - tell us which version