CODEWITHSUNDEEP
sqlvb.netdatetimepickerdatetime-format
Jad
Jad

Reputation: 91

Converting Date to string with DateTimePicker

        Dim sql As String = "SELECT * FROM old where inputdate  BETWEEN '" + DateTimePicker2.Value.ToShortDateString() + "' AND '" + DateTimePicker3.Value.ToShortDateString() + "';"
        Dim dataadapter As New SqlDataAdapter(sql, connection)
        Dim ds As New DataSet()
        connection.Open()
        dataadapter.Fill(ds, "old_table")
        connection.Close()

I have 2 DateTimePickers of format Short. In SQL, the column name "inputdate" of DataType: date. When I choose dates with days <= 12, everything is working fine. When I choose dates with days > 12, I am having this error. I can see it's a matter of day and month but i'm still can't get the solution. Any help is really appreciated": Conversion failed when converting date and/or time from character string.

Upvotes: 0

Views: 1290

Answers (4)

user4267260
user4267260

Reputation:

The basic solution is that you have to provide date into either mm/DD/YYYY format or in YYYY-MM-DD date format into ms-sql query.

So, before passing date to query convert your date into either mm/DD/YYYY format or in YYYY-MM-DD format.

Upvotes: 0

Abdellah OUMGHAR
Abdellah OUMGHAR

Reputation: 3745

I advice you to use the Parameter to use SqlDbType.DateTime and then pass the DateTime directly to the parameter (do not need to convert) , and also avoid SQL injections , like this :

Dim sql As String = "SELECT * FROM old where inputdate BETWEEN @startDate AND @endDate;"
Dim cmd As New SqlCommand(sql, connection)
cmd.Parameters.Add("@startDate", SqlDbType.DateTime).Value = DateTimePicker2.Value
cmd.Parameters.Add("@endDate", SqlDbType.DateTime).Value = DateTimePicker3.Value
Dim dataadapter As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
connection.Open()
dataadapter.Fill(ds, "old_table")
connection.Close()

Upvotes: 4

Beldi Anouar
Beldi Anouar

Reputation: 2180

Try this:

Dim dt1 as sring = String.Format("{0:yyyy/MM/dd}", Convert.ToDateTime(DateTimePicker2.Value.ToShortDateString()))

  Dim dt2 as sring = String.Format("{0:yyyy/MM/dd}", Convert.ToDateTime(DateTimePicker3.Value.ToShortDateString()))

Dim sql As String = "SELECT * FROM old where inputdate  BETWEEN '" + dt1 + "' AND '" + dt2 + "';"
        Dim dataadapter As New SqlDataAdapter(sql, connection)

Upvotes: 1

Behzad
Behzad

Reputation: 877

the output format of ToShortDateString() is not valid for sql server, and sql mixes the days with months.

try this

 Dim sql As String = "SELECT * FROM old where inputdate  BETWEEN '" + DateTimePicker2.Value.ToString("yyyy-MM-dd") + "' AND '" + DateTimePicker3.Value.ToString("yyyy-MM-dd") + "';"

read this more more information.

Upvotes: 1

Related Questions