CODEWITHSUNDEEP
sqlsql-serverdatetimecoldfusion
Scott
Scott

Reputation: 457

Coldfusion Server Time vs. SQL Database Server time (problems)

I have an issue where I need to get responses from users via email, SMS, app, etc. and each medium has their own server times. I am running into problems when these times are off by just 5-10 seconds.

I have been using ColFusion's #now()# function, but just having these issues. I would like each environment to reference just one standardized time. Best I could think of is the SQL database since they all share that resource?

I am performing update statements similar to the following:

    UPDATE
    WENS.dbo.SMS_MESSAGES 
    SET sms_reply = <cfqueryparam cfsqltype="cf_sql_varchar" value="#firstwordmessage#" />
    <cfif LEN(firstwordmessage) neq LEN(msg)>, sms_reply_notes = <cfqueryparam cfsqltype="cf_sql_varchar" value="#msg#" /></cfif>
    , sms_reply_time = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">

I would like to replace #now()# with some kind of getdate() function.

Any help with this would be greatly appreciated.

Thanks so much!!!!

Upvotes: 1

Views: 64

Answers (2)

Dan Roberts
Dan Roberts

Reputation: 4694

Instead of passing a query param based on CF server time, just call SQL Server's GetDate() function.

UPDATE WENS.dbo.SMS_MESSAGES 
SET sms_reply = <cfqueryparam cfsqltype="cf_sql_varchar" value="#firstwordmessage#" />
    <cfif LEN(firstwordmessage) neq LEN(msg)>
        , sms_reply_notes = <cfqueryparam cfsqltype="cf_sql_varchar" value="#msg#" />
    </cfif>
    , sms_reply_time = GetDate()

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20804

SQL Server gives you choices for the current date and time. They are described here:

https://learn.microsoft.com/en-us/sql/t-sql/functions/current-timestamp-transact-sql?view=sql-server-ver15

I always preferred getdate() because it was easiest to type.

Upvotes: 2

Related Questions