insert date into table

[JoeyTMann]

having some trouble inserting a date into a smalldatetime field in a MS SQL
table. I get this as an error ODBC Error Code = 22008 (Datetime field
overflow) 22008 [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error
converting datetime from character string. here is what I use to get the
date. #DateFormat(CreateODBCDateTime(now()), 'm/d/yy')# I have also tried
#CreateODBCDateTime(Now())# and I get the same error

[paross1]

Instead of #now()#, you might try using getdate() directly, since it returns
the current date and time as a datetime object (unless your SQL Server is on a
different time than your ColdFusion server).

FYI, I tested inserting a datetime and smalldatetime in a SQL Server database
and did not get any errors using #now()#, #CreateODBCDateTime(Now())#, or
getdate(), so I'm not sure why you are getting errors.

Phil

[paross1]

The getdate() function is not a ColdFusion function, but a SQL Server one, so
you should be able to use it in a query with enclosing it in any # characters.
(For Oracle, you could use sysdate(), in Access use now() for datetime or
date() for date only, and it looks like MySQL can use sysdate(), now(), or
current_timestamp).

[JoeyTMann]

OH ok. Stil not sure on this one. I can use the CreateODBCDateTime(now()) only
if I have the column datatype to be varchar. When I pick timestamp, datetime,
or smalldatetime I can't change the length. I am wondering since
CreateODBC...outputs something like {ts '2005-03-21 12:54:08'} which is a lot
longer than the 4-8 data lenghts of the datatypes. Should I have two seperate
columns one called date with 03/21/05 and another called time with 12:00 in it?

[paross1]

Don't confuse what is displayed whn you select a datetime column with what is
actually stored there. They are NOT the same thing. Each relational database
has its own way of storing date or datetime datatypes internally, which are
"converted" to something resembling a text date and time value when selected
and displayed. In order to insert a date into a datetime column, you either
need to insert a datetime object, or convert a "text" date to one using a
function, such as CreateODBCDateTime() when using an ODBC datasource. If your
column is VARCHAR then you shouldn't use CreateODBCDateTime(), as a text value
for a date should be stored without error, but I don't know how your particular
datrabase is configured. If you are using CFQUERYPARAM or CFPROCPARAM the
CFSQLtype would be CF_SQL_TIMESTAMP

From SQL Server BOL:

datetime
Date and time data from January 1, 1753 through December 31, 9999, to an
accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or
0.00333 seconds). Values are rounded to increments of .000, .003, or .007
seconds, as shown in the table.

smalldatetime
Date and time data from January 1, 1900, through June 6, 2079, with accuracy
to the minute. smalldatetime values with 29.998 seconds or lower are rounded
down to the nearest minute; values with 29.999 seconds or higher are rounded up
to the nearest minute.

Values with the datetime data type are stored internally by Microsoft SQL
Server as two 4-byte integers. The first 4 bytes store the number of days
before or after the base date, January 1, 1900. The base date is the system
reference date. Values for datetime earlier than January 1, 1753, are not
permitted. The other 4 bytes store the time of day represented as the number of
milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less precision
than datetime. SQL Server stores smalldatetime values as two 2-byte integers.
The first 2 bytes store the number of days after January 1, 1900. The other 2
bytes store the number of minutes since midnight. Dates range from January 1,
1900, through June 6, 2079, with accuracy to the minute.

Phil

[JoeyTMann]

OK Important things to know. What kind of things should I look for since
#CreateODBCDateTime(Now())# throws me the original message. CF_SQL_Type should
TIMESTAMP right? Then if I used smalldatetime as the datatype of the column
that should work ok?

[paross1]

Since you didn't include the actual code you are using in your page, it is hard
to give you specifics, only generalities. You included only the error output
and one little piece of code where you were calling CreateODBCDateTime. By the
way, what version of ColdFusion are you using?

Phil

[JoeyTMann]

Sorry, I am using CF5. Here is the query block. <cfquery name='insertticket'
datasource='helpdesk'> insert into tickets (username,
comp_type,category,problem,create_date,building,so ftware,phone,room) values
(<cfqueryparam cfsqltype='cf_sql_varchar' value='#form.username#'>,
<cfqueryparam cfsqltype='cf_sql_varchar' value='#form.comptype#'>,
<cfqueryparam cfsqltype='cf_sql_varchar' value='#form.category#'>,
<cfqueryparam cfsqltype='cf_sql_varchar' value='#form.problem#'>, <cfqueryparam
cfsqltype='CF_SQL_VARCHAR' value='CreateODBCDateTime(now())'>, <cfqueryparam
cfsqltype='cf_sql_varchar' value='#form.building#'>, <cfqueryparam
cfsqltype='cf_sql_varchar' value='#form.software#'>, <cfqueryparam
cfsqltype='cf_sql_varchar' value='#form.phone#'>, <cfqueryparam
cfsqltype='cf_sql_varchar' value='#form.room#'>) </cfquery> If I change the
cfsqltype to cf_sql_timestamp thats when I get the error. The only way I have
gotten a date into the field is by using varchar as the cfsqltype and the
column is varchar.

[paross1]

This is the result of some testing that I did with various changes to the VALUE
parameter and CFSQLTYPE, against a datetime column, and a smalldatetime column
in a SQL Server 2000 database using MX 6.1:

<cfqueryparam cfsqltype="CF_SQL_varchar" value="CreateODBCDateTime(now())">
error: [Macromedia][SQLServer JDBC Driver][SQLServer]Syntax error converting
datetime from character string.

<cfqueryparam cfsqltype="CF_SQL_timestamp" value="CreateODBCDateTime(now())">
error; The cause of this output exception was that:
coldfusion.runtime.Cast$DateStringConversionExcept ion: The value
"CreateODBCDateTime(now())" could not be converted to a date..

<cfqueryparam cfsqltype="CF_SQL_timestamp" value="#CreateODBCDateTime(now())#">
Worked OK

<cfqueryparam cfsqltype="CF_SQL_timestamp" value="#now()#">
Also worked OK

Phil

[JoeyTMann]

<cfqueryparam cfsqltype='CF_SQL_timestamp' value='#CreateODBCDateTime(now())#'>

Yeah that worked. Not sure why i didn't have the ## in there...thanks for the help Phil.

[JoeyTMann]

<cfqueryparam cfsqltype='CF_SQL_timestamp' value='#CreateODBCDateTime(now())#'>

Yeah that worked. Not sure why i didn't have the ## in there...thanks for the help Phil.