[thelist] SQL: Infuriating Date/Time Insert

Raymond Camden rcamden at allaire.com
Wed Dec 27 08:43:42 CST 2000


Have you tried using CreateDate? Check out the SQL below, it's works just
fine:

<CFSET TheDate = CreateDate(2000,2,1)>

<CFQUERY NAME="d" DATASOURCE="xxxx">
	insert into tblPolls(name,datecreated)
	values('blah',#TheDate#)
</CFQUERY>

If you want to insert the current date, just change TheDate to Now().

=======================================================================
Raymond Camden, Principal Spectra Compliance Engineer for Allaire

Email   : jedimaster at allaire.com
ICQ UIN : 3679482

"My ally is the Force, and a powerful ally it is." - Yoda

> -----Original Message-----
> From: thelist-admin at lists.evolt.org
> [mailto:thelist-admin at lists.evolt.org]On Behalf Of Frank
> Sent: Tuesday, December 26, 2000 6:22 PM
> To: thelist at lists.evolt.org
> Subject: [thelist] SQL: Infuriating Date/Time Insert
>
>
>
> Re-sent 'cause I didn't see this appear the first time a couple
> of days ago.
>
> I've got an infuriating problem that has been dogging me for days. I
> keep getting a syntax error, where there should be none. Have I
> missed something? I've tried variation upon variation of trying to
> insert number, a date, a time and a time. One by one analysis shows
> that the number inserts correctly. The rest all choke. Using
> CreateODBCDate() and CreateODBCTime Results in exactly the same
> error. Should a time or date be quoted (for MS Access 2k)? I've tried
> with and without with precisly the same error.
>
> What the #^&#%! could be going on here?
>
> Thanks and Merry Christmas.
>
>
>
>
> <!--- Construct a date from the select menus (Appears as: Jan 01
> 2000) --->
>     <cfset TheDate="#Form.Month# #Form.Day# #Form.Year#">
>
> <!--- Insert the form data into the table
>        (Datatypes: Number, Date/Time, Date/Time, Date/Time) --->
>
> <cfquery name="InsertTimeAndDates" datasource="#datasource#">
>     INSERT INTO TimeAndDates (AddrJoin, Date, StartTime, EndTime)
>      VALUES(#Form.AddrJoin#, '#TheDate#', '#Form.StartTime#',
> '#Form.EndTime#')
>     </cfquery>
>
>
> Error Diagnostic Information
>
> ODBC Error Code = 37000 (Syntax error or access violation)
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
> statement.
>
> SQL = "INSERT INTO TimeAndDates(AddrJoin, Date, StartTime, EndTime)
>         VALUES(578, 'Jan 1 2000', '8:00am', '9:00am')"
>
> --
>





More information about the thelist mailing list