locked
calculating the difference between two dates RRS feed

  • Question


  • First off i am using a dataset to get one row at a time from a sql 2008 data base as shown here. works great.........
    Dim

     

    dataSet As DataSet = New DataSet()

    adapter.Fill(dataSet, currentIndex, pageSize,

    "PB")

     

    Then I access one column at a time to fill an array... (all except the date) which i can't convert to an integer.
    Dim
    dDate As Date = dataSet.Tables("PB").Rows(pageSize - 1)("Draw Date") '.ToString()

    Dim

     

    sumDate As Date

     

    Dim dbDate As Long = dDate.Ticks

     


    Dim
    sbDate As Long = sumDate.Ticks

     

    Dim avgDate As Long
    then using a select case to identify what number is the the other columns.

    If

     

    arrRecord(int) = 2 Then

    I then do this.....
    sumDate = dDate

     

    If dDate = "" Then

    this is my counter....
    int2 = int2 + 1

     

    Else

    avgDate = sbDate - dbDate I try to do this calculation and it goes to my Catch with the error.....conversion to String "" to type ' Date' is not valid 

     

    End If

    int2 = int2 + 1

     

    End If

    all i want to do is calculate simple dates ie short dates to figure an average of how many days between occurances of a particular number.
    I have tried several different formats of dates..... ie datediff doesn't work even if i dim sbDate to date and leave dbdate to date what am i doing wrong. .net seems to have a different idea on how to handle dates. Never had this problem with the older vb stuff.
    Please give me some guideance
    Thanks for your time.....
    Mike Hawkins

    Tuesday, April 21, 2009 10:47 PM

Answers

  • Dim

    dbDate As DateTime  
    Dim sbDate As DateTime  
    Dim avgDate As DateTime
    avgDate = DateDiff(DateInterval.Day, sbDate, dbDate))

    With this laid out as shown I still get an error message that says "Error 4 Value of type 'Long' cannot be converted to 'Date" 
    Is there anyway to convert a Date to an Integer?
    Yes, John the code you wrote does work on a seperate app just trying to use it in my instance. Again not to be to repetitive but would it be a lot more simpler if i could convert a date to an integer?

    Converting date to Long:

            Dim dbDate As DateTime = Now
            Dim sbDate As DateTime
            Dim sFirst As String = dbDate.ToString
            Dim lDiff As Long
            ' Date to Long
            lDiff = dbDate.Ticks
            ' Long to Date
            sbDate = DateTime.FromBinary(lDiff)
            Dim sSecond As String = sbDate.ToString
            ' Proof of code
            MsgBox(sFirst & vbCrLf & sSecond)


    Mark the thread as answered if the answer helps you. This helps others who have the same problem !
    • Marked as answer by olsofty Wednesday, April 22, 2009 12:13 PM
    Wednesday, April 22, 2009 12:11 PM

All replies

  • Hi,

    Try this code with one button on a FORM please.

    Note the USA culture specify the month of a DATE first like April 22nd. ( 04/22/2009 )

    Here in the U.K we say the 22nd of April. ( 22/04/2009 )


    Regards,

    John
    _______________________________________

    Option Strict On
    Public Class Form1
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
            My.Application.ChangeCulture("En-Gb") '<< U.K. culture.
            Dim ChristmasDay As Date = Convert.ToDateTime("25/12/2009 00:00:00")
            Dim Difference As TimeSpan = ChristmasDay.Subtract(Now)
            MessageBox.Show(Difference.TotalMinutes.ToString & " minutes to Christmas Day!!")
    
            My.Application.ChangeCulture("En-US") '<< USA culture.
            Dim Date1 As Date = Convert.ToDateTime("04/21/2009 00:00:00") 'Midnight yesterday!!
            Dim Date2 As Date = Convert.ToDateTime("05/15/2009 14:15:30") '2:15 pm + 30 seconds on May the 15th.
            Difference = Date2.Subtract(Date1)
            MessageBox.Show("Total difference in seconds = " & Difference.TotalSeconds.ToString)
    
        End Sub
    End Class
    
    Tuesday, April 21, 2009 11:11 PM
  • Try this:

     

    Dim dt1 As DateTime = DateTime.Now

     

    Dim dt2 As DateTime = DateTime.Now.AddDays(3)

    Debug.WriteLine(DateDiff(DateInterval.Day, dt1, dt2))


    www.insteptech.com
    • Proposed as answer by DeborahKMVP Wednesday, April 22, 2009 3:26 PM
    Tuesday, April 21, 2009 11:32 PM
  • Dim

    dbDate As DateTime  
    Dim sbDate As DateTime  
    Dim avgDate As DateTime
    avgDate = DateDiff(DateInterval.Day, sbDate, dbDate))

    With this laid out as shown I still get an error message that says "Error 4 Value of type 'Long' cannot be converted to 'Date" 
    Is there anyway to convert a Date to an Integer?
    Yes, John the code you wrote does work on a seperate app just trying to use it in my instance. Again not to be to repetitive but would it be a lot more simpler if i could convert a date to an integer?
    Wednesday, April 22, 2009 11:47 AM
  • Hi olsoftly,

    the DateDiff Function returns a long value. Change your code to :

            Dim dbDate As DateTime
            Dim sbDate As DateTime
    
            Dim lDiff As Long
            lDiff = DateDiff(DateInterval.Day, sbDate, dbDate)
    

    Mark the thread as answered if the answer helps you. This helps others who have the same problem !
    Wednesday, April 22, 2009 12:00 PM
  • Dim

    dbDate As DateTime  
    Dim sbDate As DateTime  
    Dim avgDate As DateTime
    avgDate = DateDiff(DateInterval.Day, sbDate, dbDate))

    With this laid out as shown I still get an error message that says "Error 4 Value of type 'Long' cannot be converted to 'Date" 
    Is there anyway to convert a Date to an Integer?
    Yes, John the code you wrote does work on a seperate app just trying to use it in my instance. Again not to be to repetitive but would it be a lot more simpler if i could convert a date to an integer?

    Converting date to Long:

            Dim dbDate As DateTime = Now
            Dim sbDate As DateTime
            Dim sFirst As String = dbDate.ToString
            Dim lDiff As Long
            ' Date to Long
            lDiff = dbDate.Ticks
            ' Long to Date
            sbDate = DateTime.FromBinary(lDiff)
            Dim sSecond As String = sbDate.ToString
            ' Proof of code
            MsgBox(sFirst & vbCrLf & sSecond)


    Mark the thread as answered if the answer helps you. This helps others who have the same problem !
    • Marked as answer by olsofty Wednesday, April 22, 2009 12:13 PM
    Wednesday, April 22, 2009 12:11 PM
  • I would like to thank all of you who have help me with my inability to grasp the obvious. The conversion and the point that in order to achieve what i wanted was to make sure that the resultant data type was used as the variable (ie  even though i was using datetime the result was occuring as a long. So i have to make sure that what ever the result is either long or datetime or date or whatever that variable has to coincide.
    Again thanks alot.
    Wednesday, April 22, 2009 12:19 PM