DateDIff / DateCompare converting and handling coldfusion time formats

Posted on 2010-01-05
Last Modified: 2012-08-13

Hi guys, can anyone tell me whats wrong with this following code?

Im grabbing a date from a database and setting it as a variable called "StartDate" with the date mask of dd/mm/yyyy.

Im then grabbing today's date from the server and setting that as "theDate". Now this seems to work..... the two dates output as

StarDate = 01/07/2010
TheDate = 05/01/2010

Then Im looking at the dateDiff between the two (see first code example - which seems to work ok.)

The two dates above output a datDiff of 114 days which is great.

Then I want to test the date with other made up dates so I set "theDate" variable to something made up (see example 2).... and thats where I encounter problems, coldfusion is treating the dd as months so i think im converting the date wrong or something. Please take a look and let me know your thoughts.

<!--- Example one --->

<!--- set variables StartDate and theDate --->

<cfset StartDate = dateformat("#getStartDateSem2.StartDate#","dd/mm/yyyy")>

<cfset theDate = dateformat(now(),"dd/mm/yyyy")>

Date Difference: #dateDiff("d","#StartDate#","#theDate#")#

<!--- Example 2 for testing different dates manually --->

<!--- set variables StartDate and theDate --->

<cfset StartDate = dateformat("#getStartDateSem2.StartDate#","dd/mm/yyyy")>

<cfset theDate = dateformat(createDate("2010","07","02"),"dd/mm/yyyy")>

Date Difference: #dateDiff("d","#StartDate#","#theDate#")#

Open in new window

Question by:CurtinProp
    LVL 9

    Accepted Solution

    I would say, only use dateformat for the displayed date, let coldfusion compare the dates without you trying to format them.

    LVL 51

    Assisted Solution

    I agree with @zadoc.  

    > Im grabbing a date from a database and setting it as a variable called "StartDate" with
    > the date mask of dd/mm/yyyy.

    That is your first problem.  When you pull the date from your date/time column, it is a date object at that point. As soon as you use DateFormat() you are actually converting that date object into a _string_.   That is where the problems start.

    > <cfset StartDate = dateformat("#getStartDateSem2.StartDate#","dd/mm/yyyy")>
    > Date Difference: #dateDiff("d","#StartDate#","#theDate#")#

    The DateDiff() function actually expects a _date_ (not a string).  So when you use it, ColdFusion must first convert the string #StartDate# back into a _date_ object.  This is problematic because most CF date functions assume U.S. dates.  So even though you're seeing the string "01/07/2010", CF actually interprets this as January 7th (not July 1st).  So that is why you get the wrong results.

    1) Always use date/time objects for comparisons, not strings.  
    2) If you need locale specific behavior for dates, use the LS date functions
    LVL 2

    Author Closing Comment

    Thanks guys, I'll compare the dates and then format the output.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
    This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now