Searching by DATE problem

  • Thread starter Thread starter les
  • Start date Start date
L

les

I have a form to search records by date of birth. The form has 3
dropdowns for day, month and year:

Dim strDOB as string = ("'" & dobday.selectedvalue & " " &
dobmonth.selectedvalue & " " & dobyear.selectedvalue & "'")

strDOB=DateTime.Parse(strDOB)

strSQL = "SELECT * FROM table1 where dob='" & strDOB & "' "

When i response.write the original string I get '04 March 1951'
When i response.write(strDOB) it gives me 04/03/1951 but the records
returned are those with the DOB: 03/04/1951.

If I select a day over 12 it gives the error:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.

Presumably I need to format strDOB as UK date format eg: 25/12/1951,
but I've tried various things and no success. Can anyone help please?

Thanks
Leslie
 
I have a form to search records by date of birth. The form has 3
dropdowns for day, month and year:

Dim strDOB as string = ("'" & dobday.selectedvalue & " " &
dobmonth.selectedvalue & " " & dobyear.selectedvalue & "'")

strDOB=DateTime.Parse(strDOB)

strSQL = "SELECT * FROM table1 where dob='" & strDOB & "' "

When i response.write the original string I get '04 March 1951'
When i response.write(strDOB) it gives me 04/03/1951 but the records
returned are those with the DOB: 03/04/1951.

If I select a day over 12 it gives the error:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.

Presumably I need to format strDOB as UK date format eg: 25/12/1951,
but I've tried various things and no success. Can anyone help please?

Thanks
Leslie
Click to expand...

A few notes:

1) DateTime has a constructor that uses separate year, month and day values.
This is
more direct than first building a string and then parsing it (and
assigning that DateTime
value to the same string??)

2) look into "parameters" for your query: then you can supply the real
DateTime value
without having to build-and-parse a string in the query (with all the
"MDY" vs. "DMY"
problems)

3) DateTime has a "ToString()" method that accepts a format parameter.
"dd/MM/yyyy"
should output the dateformat you want.

Hans Kesting
 
Also, I thought I needed to convert the string to a date, so I don't
see why I would be using "tostring" for that
 
I have managed to fix it (finally!) using this:

SELECT * FROM table1 where Convert(char(10), DOB, 103) = '" & strDOB &
"'

Leslie
 
Back
Top