The Time is #Now()#

So here is something I ran into recently. Take a look at the following queries:

<cfquery name="insertNow1" datasource="scratch">
INSERT INTO TestTable
(
TestText
,TestDateTime
)
VALUES
(
'Test without queryparam'
,#Now()#
)
</cfquery>

<cfquery name="insertNow2" datasource="scratch">
INSERT INTO TestTable
(
TestText
,TestDateTime
)
VALUES
(
<cfqueryparam value="Test with queryparam" cfsqltype="cf_sql_varchar">
,<cfqueryparam value="#Now()#" cfsqltype="cf_sql_timestamp">
)
</cfquery>

You may expect the above two queries to give you the same results, but they don't. If you don't use cfqueryparam the datetime value is only stored to the second precision.

ID TestText TestDateTime ------ ---------------------------------- ---------------------------- 1 Test without queryparam 2008-02-12 15:31:51.000 2 Test with queryparam 2008-02-12 15:31:51.717

This is because the string representation of a coldfusion.runtime.OleDateTime object is only precise to the second. (Try dumping Now() and you will see you get a time stamp similar to {ts '2008-02-12 15:31:51'}. Notice no milliseconds.) This is just something to keep in mind, especially if your application depends on timestamps.

Comments
zac spitzer's Gravatar I tend to stick with the database now functions like oracle sysdate or mssql getdate() rather than using now()

There is a *little* less overhead than parsing and passing a date (good for logging to a db).

if you have a cluster and one of your servers clock is out, it ensures dates are always consistent for application dependant timestamps.

It's not quite as portable between across databases though
# Posted By zac spitzer | 2/13/08 3:58 AM
Julian Halliwell's Gravatar @zac:

"It's not quite as portable between across databases though"

You can use CURRENT_TIMESTAMP which the ANSI SQL equivalent.
# Posted By Julian Halliwell | 2/16/08 4:06 AM
Gerald's Gravatar That bit me in the ass before. It is always a good idea to set a time stamp as a var if you are using multiple Now().
# Posted By Gerald | 2/18/08 11:09 AM
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.