[thelist] SQL: Inserting Time & Date syntax?

[jeff]

frank,

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: Frank
:
: I'm having problems figuring out the syntax for
: inserting time and date into Access2k from CF.
: Here's the scenario: The user enters data into
: a form, date formatted as 11/12/2000 or Nov
: 12, 2000 it goes in fine. Same with inserting
: 8:00am.
:
: If the user doesn't enter time or date, I want to make
: sure there's a default to prevent an error, so in my
: insert statement I have
:
: <cfif EventStartDate NEQ"">'#Trim(EventStartDate)#'
: <cfelse>'#CreateODBCDate(Now())#'
: </cfif>,
:
: <cfif EventStartTime NEQ "">'#Trim(EventStartTime)#'
: <cfelse>#CreateODBCTime('12:00:01')#
: </cfif>,
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

first of all, you're conditional syntax could use alittle polishing.
instead of checking whether or not a variable is not equal to an empty
string, simply check for a length.  otherwise the parser must first get the
value of the variable and then compare it.  with a length check it simply
runs the function and performs the logic based on the boolean returned (0 =
false, any other number = true).  also, since you're trimming before doing
the insert/update, you should probably trim() when checking if it has a
length.

<cfif Len(Trim(EventStartDate))>
  '#Trim(EventStartDate)#'
<cfelse>
  '#CreateODBCDate(Now())#'
</cfif>,

<cfif Len(Trim(EventStartTime))>
  '#Trim(EventStartTime)#'
<cfelse>
  #CreateODBCTime('12:00:01')#
</cfif>,

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: I want to insert today as the default date and
: 12:00:01 as the default time.
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

my guess is that the field you're attempting to insert the data into isn't a
datatype consistent with the datatype of the destination column in the
database (although why it works from a form is baffling).  if, as it
appears, you're using separate columns for date and time then that could
explain why you're running into problems.  instead, why not just combine
them as a datetime field?  that should make a good portion of your headaches
go away.

aside from that you should never allow the user to key in dates or times in
a plain text box.  where the precision of the data is this important you
should be using drop down menus (month, day, year, hour, minute, am/pm,
etc.).

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: What is the proper syntax for inserting a time and date?
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

it depends on the database, but for access & sybase adaptive server anywhere
it should be:

#CreateODBCDateTime(Now())#

for sybase adaptive server enterprise, ms sql server, and oracle it should
be:

#DateFormat(Now())# #TimeFormat(Now())#

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:  Why does it enter correctly from a form (get or post)
: but not directly?
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

could be any number of things.  how about giving us some more information
about the database table involved, the cf code that's the culprit, and the
error messages you're receiving.

thanks,

.jeff

name://jeff.howden
game://web.development
http://www.evolt.org/
mailto:jeff at members.evolt.org