[RESOLVED] date format problem? datatype mismatch error
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
Code:
"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.:o
Re: date format problem? datatype mismatch error
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:
vb Code:
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.
Re: date format problem? datatype mismatch error
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.
Re: date format problem? datatype mismatch error
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.
Re: date format problem? datatype mismatch error
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.:)
Code:
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 + "'";
Re: date format problem? datatype mismatch error
You just set the "Format" property as "Custom" it will work.
Re: date format problem? datatype mismatch error
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:
C# Code:
"Select * from tablename where datefieldname = #" + dtpDatePick.Value.Date.ToString("M/dd/yyyy") + "#";
or better:
C# Code:
String.Format("Select * from tablename where datefieldname = #{0:M/dd/yyyy}#", dtpDatePick.Value.Date);
Re: date format problem? datatype mismatch error
thankyou sooo much guys....