0
votes

I'm trying to convert the retrieved string of date from access database to become date type to do the calculation of months. The problem i'm having is unable to convert the retrieved string type variable to date type. I tried a few methods such as Convert.ToDateTime, DateTime.ParseExact().. still fails

My code is able to retrieve a column of the permitEndDate as string but unable to convert it to date type cause i need to pass it to my function.

Please help cause i'm new in VB and many thanks to those who read my questions.

My code:

    For i As Integer = 0 To ds.Tables("lecturer").Rows.Count - 1

        TextBox1.AppendText(ds.Tables("lecturer").Rows(i).Item(16) & vbCrLf)
        'convert string to date type
        Dim getDate As String = ds.Tables("lecturer").Rows(i).Item(16)
        TextBox1.AppendText("Parse getDate: " & getDate & vbCrLf)

        Dim expDate As DateTime = DateTime.ParseExact(getDate, "dd/MM/yyyy", CultureInfo.InvariantCulture)
        TextBox1.AppendText("Parse expDate with date type: " & expDate & vbCrLf)
        ' Dim permitEndDate As String = ds.Tables("lecturer").Rows(i).Item(16)

        'expDate = Convert.ToDateTime(permitEndDate)
        ' TextBox1.AppendText("Difference in month: " & CalculateMonth(expDate) & vbCrLf)
    Next

The function :

'calculation of months
Function CalculateMonth(ByVal endDate As Date) As Integer
    Dim result As Integer
    '1.get today date
    Dim currentDate As Date = Date.Now.ToString("dd/MM/yyyy")

    'calculation formula
    result = DateDiff(DateInterval.Month, currentDate, endDate)

    Return result
End Function

So, my error "String was not recognized as a valid DateTime." the line is : Dim expDate As Date = Date.ParseExact(getDate, "dd/MM/yyyy", CultureInfo.InvariantCulture)

1
Firstly, why is the data in the database as text if it represents dates? Access has a Date/Time data type so why not use that? - jmcilhinney
Assuming that you don't have control over the database and must accept the data as text, what format is that text in in the database and can it be null? - jmcilhinney
Also, this is crazy: Dim currentDate As Date = Date.Now.ToString("dd/MM/yyyy"). Why would you convert a Date to a String and then expect it to be implicitly converted back to a Date? If what you want is today's date with the time zeroed then you should be using Date.Today. - jmcilhinney
i do not use the date/time in access cause the form i let the user type in the date so i used string. The second of u comment the column cannot be null.Thanks for u guidance on the today date ~ the format of my date is : dd/MM/yyyy...So is there other method to display it for the calculation later on - bloom
The fact that the user enters the date is absolutely not justification for not using the Date/Time data type in Access. I would suggest using a DateTimePicker or equivalent control but even if you use a TextBox, the correct thing to do would be to validate the user's input and convert it to a Date first and then save that to the database. You can use Date.TryParse to do both the validation and the conversion. You should fix your database now and then your current issue simply goes away. - jmcilhinney

1 Answers

1
votes

Please check your column value in table

(ds.Tables("lecturer").Rows(i).Item(16))

I think that you saved a string that is not matching to be converted into a date.