[RESOLVED] date format problem? datatype mismatch error

[sunshine123]

Hi,

I have a date time picker which displays date in the long format -
Friday, April 13, 2007.

I have a table where my date field is formated as short date - 13/4/2007.

My problem is that i get an error when trying to use value property of the datetime picker when passing it in the sql statement.

it is as follows

"Select * from tablename where datefieldname='" + dtpDatePick.Value + "'";

I get a datatype mismatch error.
I need to keep the date view in the long format itself. Therefore how can I format the date when I pass it in the sql statement.

[jmcilhinney]

I thought that we'd already been through this in your other thread. Dates do NOT have formats, just like times don't have formats. The format displayed in the DateTimePicker and the format displayed in the database are just that: formats for DISPLAY. The underlying value is the same regardless of the format used to display it.

If you want to save a Date to a database then do what I showed you in your other thread and use a parameter to insert it into your SQL statement:

Dim adp As New OleDbDataAdapter("SELECT * FROM TableName WHERE DateFieldName = @DateFieldName", con)
 
adp.SelectCommand.Parameters.AddWithValue("@DateFieldName", dtpDatePick.Value.Date)

The '.Date' bit at the end there zeroes the time portion of the value to midnight.

[sunshine123]

I know, yes, it is a similar problem...but here I'm trying to select records based on the date selected by the user in teh datetimepicker. I used dtpPicker.Value as you said. I also tried dtpPicker.Value.Date as well. Its really frustrating...I get the following error even after that.

Data type mismatch in criteria expression.

[jmcilhinney]

That's because by using string concatenation the way you are the value is being interpreted as text, not a date. The text value you're supplying doesn't match the type of the column for which you're supplying it, hence a data type mismatch. That's why in your other thread I said always use parameters.

[sunshine123]

Hi,

thanks.....well i tried something like this and it worked. What I'll do next is I'll format it as you said using parameters.

string sSQL = "Select * from tablename where Day([tablename].[DateM])='" + dtpWhen.Value.Day + "'and Month([tablename].[DateM])='" + dtpWhen.Value.Month + "'and Year([tablename].[DateM])='" + dtpWhen.Value.Year + "'";

[RaviIntegra]

You just set the "Format" property as "Custom" it will work.

[jmcilhinney]

If you really, really want to use string concatenation to insert a date into an SQL statment then you should understand that the standard date format for Microsoft SQL is M/dd/yyyy, so this would work, although I recommend against using this approach:

"Select * from tablename where datefieldname = #" + dtpDatePick.Value.Date.ToString("M/dd/yyyy") + "#";

or better:

String.Format("Select * from tablename where datefieldname = #{0:M/dd/yyyy}#", dtpDatePick.Value.Date);

[sunshine123]

thankyou sooo much guys....