CODEWITHSUNDEEP
vb.netms-access
Mulomba Hibajene
Mulomba Hibajene

Reputation: 1

DatePicker is fetching from access db using mm/dd/yyyy format but I want dd/mm/yyyy format

I have the following code: 

da = New OleDbDataAdapter("SELECT * FROM [Transactions] WHERE [TransDate] =  #" & Format(StartDatePicker.Value, "dd/mm/yyyy"), myConnection)

When I pick a date with a day value greater than 12 it works normally using dd/mm/yyyy format, but when I pick day below 12, it uses mm/dd/yyy format.

I am using vb.net and ms access.

Upvotes: 0

Views: 275

Answers (3)

Gustav
Gustav

Reputation: 55831

You are using the wrong format for your string expression of the date value in SQL. Thus:

da = New OleDbDataAdapter("SELECT * FROM [Transactions] WHERE [TransDate] = #" & Format(StartDatePicker.Value, "yyyy\/mm\/dd"), myConnection)

Note please, that format has nothing to do with the format you wish to use for display of the dates.

Also, while this will work, absorb the comments on using more clever methods.

Upvotes: 0

Cetin Basoz
Cetin Basoz

Reputation: 23797

Plutonix has already explained that a date is a date, format is for humans only. Here is revised version of your code that would work regardless of culture:

Dim sql As string = <sql>SELECT * FROM [Transactions] 
  WHERE [TransDate] >= @tStart and [TransDate] < @tEnd
</sql>.Value
Dim da = New OleDbDataAdapter(sql, myConnection)

Dim start = StartDatePicker.Value.Date
Dim end = StartDatePicker.Value.Date.AddDays(1)

da.SelectCommand.Parameters.AddWithValue("@tStart", start)
da.SelectCommand.Parameters.AddWithValue("@tEnd", end)

Upvotes: 1

Mulomba Hibajene
Mulomba Hibajene

Reputation: 1

I figured out where the problem was. it was confusing to read 08/12/2016 from the database. it was read as mm/dd/yyyy while 15/12/16 was read as dd/MM/yyyy. I set the datepicker to yyyy/MM/dd. Many Thanks for the help

Upvotes: 0

Related Questions