datetime error

[andyc209]

I have a search field that searches a SQL table for results where a date matches that entered in the search box.

I get the error

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I am searching in the format DD/MM/YYYY

The code is

select * from tablename where archive=0 and (daterelease = CONVERT(DATETIME, '"& request("formname") &" 00:00:00', 102))

it seems if i do 07/07/2006 it is ok but 27/07/2006 is not. Is this a US / UK date issue? I have tried adding the LCID = 2057 for Uk at the top of the search results page but it still fails

 

[gmmastros]

Since this is SQL Server, I suggest:

Set DateFormat DMY

select * from tablename where archive=0 and   (daterelease = CONVERT(DATETIME, '"& request("formname") &" 00:00:00', 102))

Also, take a look at this thread for a more thorough explanation of date issues within sql server. thread183-1240616

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom