indofraiser indofraiser - 5 months ago 21
Vb.net Question

String To Date format

I have a

jQuery calender
which has values in an
ASP Textbox
(not editable bar via the calender)

The two calender's create a From and To date. (Including defaults)

I need to convert the
string
from
dd/mm/yyyy
to
yyyy-MM-dd
but despite trying various stack ideas get below err-


Error: Conversion failed when converting date and/or time from
character


Code:

Dim vDateFrom As Date = sDateFrom.Text
vDateFrom = vDateFrom.ToString("yyyy-MM-dd")
Dim vDateTo As Date = sDateTo.Text
vDateTo = vDateTo.ToString("yyyy-MM-dd")


The
SQL
it runs works if I override the above with:

vDateFrom = 2016-03-01
vDateTo = 2016-06-01


When I debug it appears the date is still
dd/mm/yyyy
i.e.
#6/15/2016 12:00:00 AM#


I've also included the SQL string line (test) encase I can simply change the date there.

Using command As New SqlCommand("Select COUNT(d.ID) FROM TblData As D JOIN TBLPROPERTYLIST As p On d.UPRN = p.UPRN WHERE 1 = 1 And d.SurveyDate between '" & vDateFrom.ToString & "' AND '" & vDateTo.ToString & "' And d.Field1 = " & i & " And d.Field2 = " & j & "" & vString & "", connection)


Notes:

Looked at among others:

-Adding CDate and also

-datetime format to SQL format using C#

Answer

Try using DateTime.ParseExact to convert the text to date:

Dim myDate As DateTime
myDate = DateTime.ParseExact(sDateTxt, "dd/MM/yyyy", CultureInfo.InvariantCulture)

You can parse it to text with:

Dim result As String = myDate.ToString("yyyy-MM-dd")

But I recommend you not to do that in order to insert the value in DateBase. You should use instead parameters to avoid SQL Injection attacks.

More information here: Why do we always prefer using parameters in SQL statements?
More information about ParseExact in MSDN.