473,326 Members | 2,108 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Calculation of Date in VB.NET

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

Jul 24 '06 #1
2 8389
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
Jul 24 '06 #2
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

Jul 25 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Terry | last post by:
I have used the code provided in the PreciseDateDiff function at the following Access Web link to calculate the time change date (from Daylight to Standard time and vice versa): ...
1
by: Apple | last post by:
May anyone can teach me how to calculate with date, my calculation is : Date field (2005/01) + numberic field (3) = 2005/04(YYYY/MM)
2
by: DebbieG | last post by:
I have no idea how to start with this one. I have a subform where records could look similar to: Infraction Date Points 01/01/2000 3 06/01/2002 1 Somehow, I've got to...
3
by: Lindie | last post by:
We need to track how often a book has been loaned. We enter the dates and need an automatic calculation how often in the preceeding 12 months it has been taken out. box 1: today's date box 2:...
6
by: jimfortune | last post by:
In: http://groups.google.com/group/comp.databases.ms-access/msg/60d7faa790c65db1 james.ea...@gmail.com said: :Ok, I've answered the first half of my own question. From a MSDN :article, I...
2
by: smcgrath via AccessMonster.com | last post by:
I have a table listing accounts with a closed date - our Trial balance drops the account after 7 days but our database keeps the record forever - How do I calculate the end of month less 7 days so...
1
by: c8tz | last post by:
This is a query I have created that picks up the top 3 dates for this data (for example) : Tree Pos2 Date 1 15 23/08/2005 1 20 12/02/2006 1 32 15/10/2006 ...
3
by: Gretsch | last post by:
Web, html, javascript, Hi, I need to calculate the time since this .htm file was last modified. {which I can then use in a calculation, rather than display, so days&decimals format would be OK}...
2
by: sazd1 | last post by:
I am using vb express and MsAccess as database. I am trying to write a query for the calculation of Stock. My tables are as under: PId PDate ItemId Description Price Quantity ...
4
by: prao2005 | last post by:
Q)How to do the calculation for a date? Solution applied --> Defining a template xsl:template match="DBE:Object" mode="TestTable" Delivery Date Latest Possible Order Date
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.