We help IT Professionals succeed at work.

Date difference. Date in format 20110529

pvg1975
pvg1975 used Ask the Experts™
on
Hello all!

I have a field on a database that contains an integer number. Its supposed to be a date. The format is yyyyMMdd, for example: 20110529

Is it possible to know the time difference (in months) in between the field value, and the current date?

I use asp.net (vb)

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
       DateDiff(DateInterval.Month, yourdate, Now.Today)

Author

Commented:
It didnt work, maybe cause my date is an integer value and not a date value?
Commented:
use this to convert it to a date:
Public Function Number2Date(ByVal Number As Long) As Date
Dim S As String = Number
Dim D, M, Y As Integer
M = S.Substring(0, 2)
D = S.Substring(2, 2)
Y = S.Substring(4, 4)
Return New Date(Y, M, D)
End Function

Open in new window

if yourdate is not "DATE", you can change it by:
 Dim myDate As Date = "#" + yourdate.tostring.Substring(0, 4) + "-" + yourdate.tostring.Substring(4, 2) + "-" + yourdate.tostring.Substring(6, 2) + "#"
Dim NumMonth as Integer = DateDiff(DateInterval.Month, myDate, Today())

Commented:
source: http://forums.asp.net/t/1163565.aspx/2/10?calculate+number+of+months+between+two+dates+vb+net

Then use something like this:
Public Function monthDifference(startDate As DateTime, endDate As DateTime) As Integer

	Dim systemStartDate As New DateTime()
	Dim timeDifference As TimeSpan


	If endDate > startDate Then
		timeDifference = endDate.Subtract(startDate)
	Else
		timeDifference = startDate.Subtract(endDate)
	End If


	Dim generatedDate As DateTime = systemStartDate.Add(timeDifference)
	Dim noOfYears As Integer = generatedDate.Year - 1
	Dim noOfMonths As Integer = generatedDate.Month - 1

	noOfMonths = noOfMonths + (noOfYears * 12)

	Return noOfMonths

End Function

Open in new window

Commented:
scratch that use this tested and works:
Public Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim d1 As DateTime = DateTime.Now
        Dim number As Integer = 20110529

        Dim d2 As DateTime = Number2Date(number)

        Dim M As Integer = Math.Abs((d1.Year - d2.Year))
        Dim months As Integer = ((M * 12) + Math.Abs((d1.Month - d2.Month)))

        Response.Write(months)

    End Sub

    Public Function Number2Date(ByVal Number As Long) As Date
        Dim S As String = Number
        Dim D, M, Y As Integer
        M = S.Substring(4, 2)
        D = S.Substring(6, 2)
        Y = S.Substring(0, 4)
        Return New DateTime(Y, M, D)
    End Function

    Public Function monthDifference(startDate As DateTime, endDate As DateTime) As Integer

        Dim systemStartDate As New DateTime()
        Dim timeDifference As TimeSpan


        If endDate > startDate Then
            timeDifference = endDate.Subtract(startDate)
        Else
            timeDifference = startDate.Subtract(endDate)
        End If


        Dim generatedDate As DateTime = systemStartDate.Add(timeDifference)
        Dim noOfYears As Integer = generatedDate.Year - 1
        Dim noOfMonths As Integer = generatedDate.Month - 1

        noOfMonths = noOfMonths + (noOfYears * 12)

        Return noOfMonths

    End Function
End Class

Open in new window

Author

Commented:
Thank you all for your answers. I ended up doing this think work as follows:

            Dim DatabaseValue As Integer = Expira
            Dim dt As DateTime
            If DateTime.TryParseExact(DatabaseValue, "yyyyMMdd", Nothing, Globalization.DateTimeStyles.None, dt) Then
                Dim strDT As Date = dt.ToString("M/d/yyyy")
                Label2.Text = DateDiff(DateInterval.Month, strDT, Now.Today)
            End If

Commented:
edited (don't need last function)
Public Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim d1 As DateTime = DateTime.Now
        Dim number As Integer = 20110529

        Dim d2 As DateTime = Number2Date(number)

        Dim M As Integer = Math.Abs((d1.Year - d2.Year))
        Dim months As Integer = ((M * 12) + Math.Abs((d1.Month - d2.Month)))

        Response.Write(months)

    End Sub

    Public Function Number2Date(ByVal Number As Long) As Date
        Dim S As String = Number
        Dim D, M, Y As Integer
        M = S.Substring(4, 2)
        D = S.Substring(6, 2)
        Y = S.Substring(0, 4)
        Return New DateTime(Y, M, D)
    End Function

End Class

Open in new window

Author

Commented:
           Dim DatabaseValue As Integer = 20110315
            Dim dt As DateTime
            If DateTime.TryParseExact(DatabaseValue, "yyyyMMdd", Nothing, Globalization.DateTimeStyles.None, dt) Then
                Dim strDT As Date = dt.ToString("M/d/yyyy")
                Label2.Text = DateDiff(DateInterval.Month, strDT, Now.Today)
            End If