convert date to string then back again

[philrm]

Hi everyone

I am building a Coldfusion web site and need some advise on an sql issue. To populate a date field in a data editing form I am using a select * from. However for some reason this is returning yyyy-mm-dd hh:mm:ss. When the users edit this date field they have to use mm-dd-yy format or an error is returned
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value. SQL = "UPDATE T_SOIL_SITE SET proj_code = 'GENRL', site_number = 'as123 ', collectdat = '13/05/02', surveyorone = 'NS', surveyortwo= '', site_type_cd= 'R', general_lithology_cd= 'N', geological_age_cd= '' WHERE site_id = '2887'"
What is happening??
My second question is if I use the convert statment to convert the date value to a char in dd-mm-yy format how do I get it back into the database as a date again. It needs to be a date (I think) to help the searching on date that is also done by the users. Am I confused or just stup...
Any help would be greatly appreciated

 

[JayKusch]

My CF coders use this snipette to enter dates in:

#Dateformat(CreateODBCDate(now()), "mm/dd/yyyy&quot#

it places the current time stamp in. to add the user defined date in, it may be something like this:

#Dateformat(CreateODBCDate(variable), "mm/dd/yyyy&quot#

Hope this helps