Skip to main content
Nick Way
Inspiring
May 19, 2006
Answered

updating one field with another (which contains a time stamp)

hi there

i am trying to acomplish what i thought was a simple task of updating one field in my db using the contents of another

in my tbl_profiles i have:

key | penSignedIn | lastSignedIn
73 | | 2006-05-19 21:14:56.0

I am trying to update my table so the tbl_profiles.penSignedIn field is populated with the contents of tbl_profiles.lastSignedIn

<cfquery datasource="nya">
UPDATE tbl_profiles
SET PenSignedIn = #rs_user.lastSignedIn#
WHERE profileID = 73
</cfquery>

i am getting this CF error "Syntax error (missing operator) in query expression '2006-05-19 21:14:56.0'. "

and CF says "SQL : UPDATE tbl_profiles SET PenSignedIn = 2006-05-19 21:14:56.0 WHERE profileID = 73 "

which looks ok to me!! i've tied various different formats such as
SET PenSignedIn = {ts '#MM_rsUser.lastSignedIn#'}
SET PenSignedIn = #MM_rsUser.lastSignedIn#
SET PenSignedIn = '#MM_rsUser.lastSignedIn#'

but none seem to work which is driving me up the wall!! - can anyone please point me in the right direction please

thank you very much indeed.
I have CF7 and Access database

This topic is closed to new replies. Start a new post to keep the conversation going.
paross1Correct answer
Participating Frequently
May 19, 2006
If you are just trying to update the contents of the penSignedIn column with the contents of the lastSignedIn column in the same table, do this:

<cfquery datasource="nya">
UPDATE tbl_profiles
SET PenSignedIn = lastSignedIn
WHERE profileID = 73
</cfquery>

Otherwise, if you are trying to use the value from a variable, perhaps you need to create a date/time object:

<cfquery datasource="nya">
UPDATE tbl_profiles
SET PenSignedIn = #createodbcdatetime(rs_user.lastSignedIn)#
WHERE profileID = 73
</cfquery>

Phil
View reply

4 replies

shilpsk-uK8bmf
Participating Frequently
May 19, 2006
This tech note might solve your problem.

http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=tn_18324

Try "Use string functions and/or date and time formatting functions to remove the fractional seconds from the returned string and format the data for output."

Translate
paross1Correct answer
Participating Frequently
May 19, 2006
If you are just trying to update the contents of the penSignedIn column with the contents of the lastSignedIn column in the same table, do this:

<cfquery datasource="nya">
UPDATE tbl_profiles
SET PenSignedIn = lastSignedIn
WHERE profileID = 73
</cfquery>

Otherwise, if you are trying to use the value from a variable, perhaps you need to create a date/time object:

<cfquery datasource="nya">
UPDATE tbl_profiles
SET PenSignedIn = #createodbcdatetime(rs_user.lastSignedIn)#
WHERE profileID = 73
</cfquery>

Phil
Translate
Nick WayAuthor
Inspiring
May 19, 2006
Thank you both for your replies - for my particular situation Phil's suggestion here did the trick - thank you very much, i didn't know you could do that

quote:
Originally posted by: paross1
If you are just trying to update the contents of the penSignedIn column with the contents of the lastSignedIn column in the same table, do this:

<cfquery datasource="nya">
UPDATE tbl_profiles
SET PenSignedIn = lastSignedIn
WHERE profileID = 73
</cfquery>

Otherwise, if you are trying to use the value from a variable, perhaps you need to create a date/time object:

<cfquery datasource="nya">
UPDATE tbl_profiles
SET PenSignedIn = #createodbcdatetime(rs_user.lastSignedIn)#
WHERE profileID = 73
</cfquery>

Phil


Translate
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices