Searching by DATE problem

[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

[Hans Kesting]

<les> wrote in message news:89********************************@4ax.com...

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

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

[les]

Thanks Hans,
But how would I apply "ToString()" in this case?
On Tue, 30 Dec 2003 09:45:14 +0100, "Hans Kesting"
<ne***********@spamgourmet.com> wrote:

<les> wrote in message news:89********************************@4ax.com...

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

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

[les]

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
On Tue, 30 Dec 2003 10:25:17 +0000, les wrote:

Thanks Hans,
But how would I apply "ToString()" in this case?
On Tue, 30 Dec 2003 09:45:14 +0100, "Hans Kesting"
<ne***********@spamgourmet.com> wrote:

<les> wrote in message news:89********************************@4ax.com...

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

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

[les]

I have managed to fix it (finally!) using this:

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

Leslie
On Tue, 30 Dec 2003 12:29:07 GMT, le*@asasdad.com (les) wrote:

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
On Tue, 30 Dec 2003 10:25:17 +0000, les wrote:

Thanks Hans,
But how would I apply "ToString()" in this case?
On Tue, 30 Dec 2003 09:45:14 +0100, "Hans Kesting"
<ne***********@spamgourmet.com> wrote:

<les> wrote in message news:89********************************@4ax.com...
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

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