DateTime issues w/ Coldfusion/MySQL

[josheby]

I am having a BIG problem and have not been able to find any help so far. I am not sure if this is a Coldfusion issue or a MySQL issue, but I figured enough people use MySQL i cannot be the only one that has had this problem...

I have some code that updates a field in my database with the current date/time upon logging into the site:
<cfquery datasource="dsn">
UPDATE users
SET userLastLogin = "#LSDateFormat(Now(),"yyyy-mm-dd")# #LSTimeFormat(Now(),"HH:mm:ss")#"
WHERE userID = #login.userID#
</cfquery>

This happens and when I look in my database I will see: 2008-07-02 17:12:33

But when I use cfquery and get a list of users I cannot use LSTimeFormat or TimeFormat on the value of this field because it returns this: 2008-07-02 17:12:33.0

I can use LSDateFormat and DateFormat, but if I do anything pertaining to the time I get:
'' is an invalid time format.

Any clues? I really need to get this figured out so all help is greatly appreciated! THANKS!

[JR__Bob__Dobbs-qSBHQ2]

I am confused by your post. Your sample code is an UPDATE sql statement.

quote:

---

<cfquery datasource="dsn">
UPDATE users
SET userLastLogin = "#LSDateFormat(Now(),"yyyy-mm-dd")# #LSTimeFormat(Now(),"HH:mm:ss")#"
WHERE userID = #login.userID#
</cfquery>

---

However your problem would seem to related to SELECTs within a cfquery and/or a cfoutput to display the data.

quote:

---

But when I use cfquery and get a list of users I cannot use LSTimeFormat or TimeFormat on the value of this field because it returns this: 2008-07-02 17:12:33.0

---

To clarify:
1. Does your UPDATE statement fail or work incorrectly?
2. If your problem relates to a cfquery SELECT to "get a list of users" can you post that code?
3. What database data type is userLastLogin from the users table?
4. Can you post the LSDateFormat or DateFormat related code that fails?

[josheby]

Sorry about the confusion...

1. Yes the UPDATE works. It runs as part of the login process and works fine. Value looks fine in my database via NaviCat.

2. Here is the SELECT statement that is reading the data back from the database:
<cfquery datasource="dsn" name="users">
SELECT users.*, directory.directoryID, directory.userID, status.userStatus
FROM users AS users
LEFT JOIN directory AS directory ON directory.userID = users.userID
LEFT JOIN users_status AS status ON users.userStatusID = status.userStatusID
ORDER BY users.userLastName ASC, users.userFirstName ASC
</cfquery>

(I know it says "users AS users"... the table name is longer, i shortened them to make it more readable...)

3. The field type is datetime. I have used this type tons and never seen this before. The format should be "0000-00-00 00:00:00" (at least it always has been...)

4. The code that fails is:
#LSDateFormat(users.userLastLogin,"m/d/yy")# #LSTimeFormat(users.userLastLogin,"h:mm tt")#

It is having no trouble with the date... just the time... I have tried LSTimeFormat, TimeFormat, LSParseDateTime and they all fail due to an invalid time format.

If I just output #users.userLastLogin# I get:
2008-07-02 17:12:33.0

I am assuming the ".0" on the end is the culprit, but have no clue where its coming from?

[JR__Bob__Dobbs-qSBHQ2 · Accepted solution]

Is it possible that your resultset contain a row where userLastLogin is NULL? Passing a non-date value to DateFormat would cause the error you are getting.

Try this code:
<cfif IsDate(users.userLastLogin)>#DateFormat(users.userLastLogin,"m/d/yy")#<cfelse>???</cfif>

[josheby]

Talk about feeling stupid right now...lol I guess I need a break...

Thanks for your help... I didn't even have to read through your entire last post before my forehead hit my desk and I started laughing at myself...

Thanks again!