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.

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.

Does anyone there to help me out.

I thank you for your kind understanding for reading my post.

Thank you.

Rgds,
GC

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
(E-Mail Removed)
The address is real, but unread - please reply to the group
For VB and C# questions - tell us which version

Hi Larry,

Thank you for your correction about "June. (Which is the 30th, not the 31st!)
"
And your undrstanding is coreect about my needs.

In short I will get a day and subtrct from the 30 June 2006.
As an example If I say I get Datediff as 3 months 14 days then I have to put
my decimal value into cl3_6Month column.

Unfortunatly when I use DateDiff in VB.NET I will have to get the value in
Long dataType. So the 3 Months 14 Days will only show as 3 which is not
correct value that I am looking for.

I don't need to use the DateSerial. Because I will know the Report Date
before hand.
And I will connect AS400 and get my second date (DueDate) and subtract them
to see diffrences between them so I know where I can put my decimal value in
the given column.

Again:
----------------------------------------------------------------------------------------
Dim ReportsDate as Date = "#30/6/2006#"
Dim DueDate as date = "#14/9/2006#"

Dim resultDiff as Double = DateDiff(DateInterval.Month, ReportsDate ,
DueDate )

If resultDiff =<1 Then
mBalance1 = mBalance1 + mBValue
ElseIf (resultDiff > 1) And (resultDiff =<3) Then
mBalance2 = mBalance2 + mBValue
.....
.....
.....
End If

From variable mBalance? I will know which column I will put my value in on
newRow

But unfortunatly I cannot oberload the DateDiff to reprsent Double value
that I need the fraction.

I hope this post most clear than before and I thank you for your kind
understanding to helping me.

Rgds.
GC