createodbcdatetime() function.

[HeloWorld]

Hi everyone,

if I use the following;
<cfset mydatetime = #createodbcdatetime(now())#>

and try to insert the "mydatetime" value into a MS-SQL Database, the DB throws
an error about the value not meeting the datetime columns length or some such
error.

I thought the whole idea of the createodbc function set was to create
date/time values that were DB friendly.
What am I missing??

[Dan Bracuk]

I never use the createodbcsomething functions. I always do this sort of thing,
and I probably have the wrong timeformat.

datetimestring = dateformat(now(), "yyyy-mm-dd" & " " & timeformat(now(),
"HH:MM:ss";

(should resemble 2005-09-06 20:53:00)

Then in the query, do something like this:

insert into thetable
(datetimefield, etc)
values
( {ts '#datetimestring#'}, etc)

[HeloWorld]

Originally posted by: Dan Bracuk
I never use the createodbcsomething functions. I always do this sort of
thing, and I probably have the wrong timeformat.

datetimestring = dateformat(now(), "yyyy-mm-dd" & " " & timeformat(now(),
"HH:MM:ss";

(should resemble 2005-09-06 20:53:00)

Then in the query, do something like this:

insert into thetable
(datetimefield, etc)
values
( {ts '#datetimestring#'}, etc)



Using your code and adding in the "{ts...} " is the problem I am having.
the "{ts}" needs to be removed from the string prior to being inserted into
the MS-SQL db, otherwise it fails.

[mxstu]

Originally posted by: HeloWorld
Using your code and adding in the "{ts...} " is the problem I am having.
the "{ts}" needs to be removed from the string prior to being inserted into
the MS-SQL db, otherwise it fails.


I've used createODBCDate() and createODBCDateTime() with MS SQL for years and
it works fine. Although, Dan Bracuk's date format looks pretty similar to
ODBC format. Just make sure you're not putting single quotes around the
variable values. It should look something like this

<!--- don't need # pounds signs around the CF functions --->
<cfset mydatetime = createodbcdatetime(now())>

<cfquery .... >
INSERT INTO yourTable (someDateColumn)
VALUES (#myDateTime#)
</cfquery>

[HeloWorld]

Seems that I should take myself out the back and slap me around a little.....

I am not too sure how it works, but it does....

Ultimately , I believed it wasn;t working due to the following.

when I CFOUPUTted the variable, it displayed as {ts'sxxxx'}
I tried ti cut/paste THIS value directly into the SQL database (for testing to
see if it worked!)
which it didn't.

performing a SQL insert, somehows strips out the {ts' '} and just leaves
you with the date/time.

[mxstu]

I think that's what everyone thinks the first time they see that funky date/time format... "This can't *possibly* work right..." ;-)