String To Date format

Question

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#

You should work with parameters in your sql-command. This would propably solve the problem. — etalon11, Jun 15 '16 at 07:32
You should never rely on culture dependent datetime formats. You might [read this](http://stackoverflow.com/a/34275965/5089204) if you really have to. But anyway your query should insert the values as **parameter**. It is very bad habit to concatenate a query like you do it here... — Shnugo, Jun 15 '16 at 07:32
Once you have the data stored in a `Date` or `DateTime` variable, keep *using* `datetime` types. It's your insistence on going back to *strings* that are *causing* your formatting issues. As etalon said, use parameters so that ADO.Net can take your `DateTime` values in VB.Net and translate them directly into `datetime` values in SQL Server. — Damien_The_Unbeliever, Jun 15 '16 at 07:34
Dates do not have a format. The NET providers are perfectly capable of passing DateTime variables to the DB using parameters without any gyrations at all. They will work fine provided the db column is also a Date type. Also, you should turn on Option Strict - some of that code will not compile. — Ňɏssa Pøngjǣrdenlarp, Jun 15 '16 at 12:23

score 2 · Answer 1 · edited Jul 29 '20 at 18:42

1. Turn on Option Strict

Dim vDateFrom As Date = sDateFrom.Text
' or:
vDateFrom = "2016-03-01"

Code such as this will not compile: you cannot assign a string to a date or vice versa. They are 2 different things. In most cases, a string ought to be parsed to a DateTime type.

2. Dates Do Not have a Format

I need to convert the string from dd/mm/yyyy to yyyy-MM-dd

No, you dont. Dates do not have a format. MSDN explains that a DateTime is simply a value:

Represents an instant in time, typically expressed as a date and time of day.

In the Remarks:

Time values are measured in 100-nanosecond units called ticks, and a particular date is the number of ticks since 12:00 midnight, January 1, 0001 A.D. (C.E.) in the GregorianCalendar calendar (excluding ticks that would be added by leap seconds). For example, a ticks value of 31241376000000000L represents the date, Friday, January 01, 0100 12:00:00 midnight.

Any format is just how that instant in time is expressed and displayed to humans - which usually involves changing the Date to a string - so it is strings/text and not Dates which have a format. Which means you only have to worry about a format when passing strings to the database (which should be avoided).

A date can be used in a query as a Datetime type, most DBProviders are quite capable of passing the data and converting to however it is stored - that is its job. This assumes the db column is also a date type.

3. The Debugger uses Invariant Culture

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

The VB IDE displays the date to you in the same format you must use when defining a date literal: dd/MM/yyyy. This is meant to remove ambiguity and be consistent.

Dates do not have a format, so #6/4/1944# is the same instant in time whether it is displayed that way or June 4, 1944. Both have the same meaning.

4. Use SQL Parameters Always

Not only will this protect against SQL Injection attacks, it will make your code easier to read and prevent undesired type conversions.

Dim SQL = <sql>
          Select ...  
          WHERE 1 = 1 And d.SurveyDate between @p1 AND @p2 
          AND #fld1 = @p3 AND #fld2 = @p4"
      </sql>.Value

For a complex query, you can use an XML literal so you can format the query to make it more readable. There are no gyrations for ticks and string concatenation or continuation and no chance of accidentally converting a Date or integer to text with extra ticks where they are not needed.

You cannot use parameters for column names. So if you need to provide those, I would add them when the SQL is created, but you could also use String.Replace:

' no idea what these are, probably need .ToString for Option Strict
SQL = SQL.Replace("#fld1", d.Field1).Replace("#fld2", d.Field3)

Then, provide the variables via parameters (this uses MySql):

Using cmd As New MySqlCommand(SQL, connection)
    cmd.Parameters.Add("@p1", MySqlDbType.Date).Value = dtFrom.Date
    cmd.Parameters.Add("@p2", MySqlDbType.Date).Value = dtTo.Date
    cmd.Parameters.Add("@p3", MySqlDbType.Int32).Value = i
    cmd.Parameters.Add("@p3", MySqlDbType.Int32).Value = j
    ...
End Using

Be sure to use the correct type as the xxxDbType: Many DB provides have a Date item which will pass only the date while DateTime will include the time elements which is often not wanted in BETWEEN queries. Passing the date only (dtFrom.Date) assures that as well.

You may want to read What do BETWEEN and the devil have in common? which explains why you may want to use a "dtTarget > @p1 AND dtTarget < @p2" query rather than BETWEEN.

Fabio · Answer 2 · 2016-06-15T08:24:22.227

Convert String to Date only once. And use variable of type Date for Sql query.
Use SqlParameter for passing values to the sql query, with parameters you avoid all kind of formatting problems, also parameters will defend from Sql injection.
Always use SqlParameter

Use Date.ParseExact for converting string to Date

Dim provider As CultureInfo = 
    System.Globalization.CultureInfo.InvariantCulture
Dim format As String = "dd/MM/yyyy"

Dim dateFrom As Date = Date.ParseExact(sDateFrom.Text, format, provider)
Dim dateTo As Date = Date.ParseExact(sDateTo.Text, format, provider)

Then use converted values in Sql query with parameters

Dim query As String = 
    "Select COUNT(d.ID) FROM TblData As D
     JOIN TBLPROPERTYLIST As p On d.UPRN = p.UPRN
     WHERE d.SurveyDate between @DateFrom AND @DateTo"

Dim parameters As SqlParameter() = 
{
    New SqlParameter With {.ParameterName = "@DateFrom", 
                           .SqlDbType = SqlDbType.DateTime,
                           .Value = dateFrom},
    New SqlParameter With {.ParameterName = "@DateTo", 
                           .SqlDbType = SqlDbType.DateTime,
                           .Value = dateTo}
}
Using command As New SqlCommand(query, connection)
    command.Parameters.AddRange(parameters)
    'Execute command
End Using

score 0 · Accepted Answer · edited May 23 '17 at 12:22

0

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.

edited May 23 '17 at 12:22

Community

1
1

answered Jun 15 '16 at 07:57

SysDragon

9,692
15
60
89

Again, once you have a perfectly usable `DateTime` value, all that converting it back into a string does is *introduce* opportunities for formatting issues to arise. – Damien_The_Unbeliever Jun 15 '16 at 08:00
why the downvote? there is any problem with the answer? – SysDragon Jun 21 '16 at 07:19

String To Date format

3 Answers3

1. Turn on Option Strict

2. Dates Do Not have a Format

3. The Debugger uses Invariant Culture

4. Use SQL Parameters Always