Skip to main content
J
josheby
Inspiring
July 2, 2008
Answered

DateTime issues w/ Coldfusion/MySQL

  • July 2, 2008
  • 1 reply
  • 2028 views
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!
    This topic has been closed for replies.
    Correct answer JR__Bob__Dobbs-qSBHQ2
    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>

    1 reply

    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    July 2, 2008
    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?
    J
    joshebyAuthor
    Inspiring
    July 2, 2008
    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?
    J
    JR__Bob__Dobbs-qSBHQ2Correct answer
    Inspiring
    July 2, 2008
    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>
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices