[thelist] SQL: Infuriating Date/Time Insert

[Warden, Matt]

> What the #^&#%! could be going on here?
...
>     <cfset TheDate="#Form.Month# #Form.Day# #Form.Year#">

I'm no CF man, but can you make a variable of datatype Date and then output it
as a string when you construct your SQL statement? It's nice because if date
formats change or a new format is added, you don't have to change all your
code... Allaire would probably just patch it. Plus, you don't have to worry
about the format needed.

> <cfquery name="InsertTimeAndDates" datasource="#datasource#">
>     INSERT INTO TimeAndDates (AddrJoin, Date, StartTime, EndTime)
>      VALUES(#Form.AddrJoin#, '#TheDate#', '#Form.StartTime#',
'#Form.EndTime#')
>     </cfquery>
...
> SQL = "INSERT INTO TimeAndDates(AddrJoin, Date, StartTime, EndTime)
>         VALUES(578, 'Jan 1 2000', '8:00am', '9:00am')"

I think you need to use # to delimit dates. I seem to remember that in CF you
need to use two ## because # is a form delimiter. So, would it be something
like this? :

<cfquery name="InsertTimeAndDates" datasource="#datasource#">
    INSERT INTO TimeAndDates (AddrJoin, Date, StartTime, EndTime)
    VALUES(#Form.AddrJoin#, ###TheDate###, ###Form.StartTime###,
###Form.EndTime###)
</cfquery>


Hope this points you in the right direction. If it doesn't work, try using the
'#Jan 01 2000#' format. I know it's one of those.



--
mattwarden
mattwarden.com