custom date format causing query error

Question

Wondering if anyone can explain what's causing this query to return incorrect data when getting information from my datetimepicker form

On form load the following command takes place

Private Sub frm_3_viewincident_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'clears the start date dtp box
    dtp_startdate.Visible = True
    dtp_startdate.Format = DateTimePickerFormat.Custom
    dtp_startdate.CustomFormat = " "
end sub

This makes the datetimepicker blank as intended,

When the value is changed I have the following command taking place

Private Sub dtp_startdate_ValueChanged(sender As Object, e As EventArgs) Handles dtp_startdate.ValueChanged
    'changes format to dd/mm/yyyy
    dtp_startdate.Format = DateTimePickerFormat.Custom
    dtp_startdate.CustomFormat = "dd/MM/yyyy"
End Sub

This then displays the selected date as 15/10/2014 for example. Now if I enter the date 1st of March 2015 as 01/03/2015 and run the following query

select * from incident where [incident date] > #" & dtp_startdate.Text & "#

instead of returning any results from 1st of march onwards it's returning all results from the 3rd of january onwards. The database is in the short date format dd/mm/yyyy same as the dtp format so I am unsure whats causing this. Can anyone advise?

if the DB column is a date type use dtp_startdate.Value because dates do not have a format. if it is not a date it should be if you want to do comparisons like >, = etc. Otherwise you are doing a text compare where "9" is always greater than "12" — Ňɏssa Pøngjǣrdenlarp
Thanks for the response, upon doing this I have the following results. Changing the dtp to .value instead of text gives the same result, if I remove the # from the query I get the following message show: Syntax error (missing operator) in query expression '([incident date] > 01/03/2015 15:15:29)'. — Boneyt
depending on what and how incident_date is stored, you might want dtp.Value.Date in the query to pass just the Date and not Time portion. — Ňɏssa Pøngjǣrdenlarp
tried and still shows results for anything dated after 3rd of jan, this is really strange. — Boneyt
the formats have little (if anything) to do with it because dates do not have formats - they are simply a value (a number of Ticks). Formatting is how the date is only how they are displayed. If you are passing strings to the DB on INSERT, the DB can convert/misinterpret. If there is no data after 1/3/2015 then you will get nothing back. Very hard to tell from here — Ňɏssa Pøngjǣrdenlarp

apc apc · Accepted Answer · 2015-03-16T16:12:42

Try using

"select * from incident where [incident date] > #" & dtp_startdate.Value.ToString("yyyy-MM-dd") & "# "

as this date format is less ambigous then dd/MM/yyyy or MM/dd/yyyy

You should also consider using parameters in you query instead of string concaternation.

custom date format causing query error

1 Answers