Skip to main content
A
Anonymous
August 18, 2008
Answered

Access DB Dates

  • August 18, 2008
  • 4 replies
  • 1700 views
I have a query that reteives dates out of an Access database. What I am trying to do is select 1 date out of many. The query I would like to use, looks like something as follows:

<cfset TodaysDate = #CreateODBCDate(Now())#>

<CFQUERY DATASOURCE="#DataSource#" name="GetDates">
SELECT WeekEndingDate
FROM Productivity
WHERE JobNumber = '#URL.JobNumber#' AND
SubJobNumber = '#URL.SubJobNumber#' AND
WeekEndingDate <= #TodaysDate#-7
</CFQUERY>

The dates it has to choose from is:

8-17-08
8-24-08
9-7-08
9-14-08
9-21-08
9-28-08
10-5-08

When I try:

<cfoutput query="GetDates">#DateFormat(WeekEndingDate, "medium")#<br></cfoutput>

I get all the dates. I have tried numerous formats and no luck. The dates in the database are in ODBC format currently. Any suggestions would be wonderful! Thanks in advance!
    This topic has been closed for replies.
    Correct answer
    quote:
    Originally posted by: chrispilie
    I changed the datatype in the database to date. I am now getting an empty string with the cfdump.

    It's possible that you lost all your old string values when you changed the datatype. That's ok, they were useless anyway.

    Open your table in Access and populate a few fields. Then test your query again.

    You solved it! Thanks! I had been using the text datatype and it had worked till now. Thanks for pointing me to the right way.

    4 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    August 19, 2008
    What is the datatype of the column WeekEndingDate? How is the data entered in the column? You have shown us 8-17-08 and Aug 17, 2008. Which is it?


    A
    Anonymous
    August 19, 2008
    It reads in the database {d '2008-08-17'}. The datatype in Access is 'Text'. I am displaying it with a DateFormat() Function.
    D
    Dan_Bracuk
    Inspiring
    August 19, 2008
    quote:
    Originally posted by: chrispilie
    It reads in the database {d '2008-08-17'}. The datatype in Access is 'Text'. I am displaying it with a DateFormat() Function.

    The datatype is the source of your problem. Storing dates as text is a very bad idea, as you are now finding out.
    D
    Dan_Bracuk
    Inspiring
    August 18, 2008
    To see what is happening, add #dateadd("d",7,todaysDate)# to your select clause and then use cfdump to look at your results.

    In this specific case, it probably does not matter too much, but it's often a good idea to assign work to the db instead of cold fusion. MS Access has all the date functions necessary to do the date filtering without cold fusion variables.

    A
    Anonymous
    August 19, 2008
    This is what I did as Dan instructed.

    <cfset TodaysDate = #CreateODBCDate(Now())#>
    <!--- Update Productivity with spent totals --->
    <CFQUERY DATASOURCE="#DataSource#" name="GetDates">
    SELECT WeekEndingDate, #dateadd("d",7,todaysDate)#
    FROM Productivity
    WHERE JobNumber = '#URL.JobNumber#' AND
    SubJobNumber = '#URL.SubJobNumber#' AND
    WeekEndingDate <= #dateadd("d",7,todaysDate)#

    </CFQUERY>

    <cfdump var="#GetDates.WeekEndingDate#"><br />
    <cfoutput query="GetDates">#DateFormat(WeekEndingDate, "medium")#<br></cfoutput>

    This was the output.

    {d '2008-08-17'}
    Aug 17, 2008
    Aug 24, 2008
    Aug 31, 2008
    Sep 7, 2008
    Sep 14, 2008
    Sep 21, 2008
    Sep 28, 2008
    Oct 5, 2008
    Oct 12, 2008
    Oct 19, 2008
    Oct 26, 2008

    Why is there a difference?
    P
    paross1
    Participating Frequently
    August 19, 2008
    Does this work any better since your WeekEndingDate field is type text?

    SELECT WeekEndingDate, #dateadd("d",7,todaysDate)#
    FROM Productivity
    WHERE JobNumber = '#URL.JobNumber#'
    AND SubJobNumber = '#URL.SubJobNumber#'
    AND CDate(WeekEndingDate) <= #dateadd("d",7,todaysDate)#

    CDate() is an Access function that will convert any value to a date as long as the expression is a valid "date".

    Phil
    A
    Anonymous
    August 18, 2008
    Thanks for thequick reply! This is what I switched it to. I am still getting all the dates.

    <cfset TodaysDate = #CreateODBCDate(Now())#>

    <CFQUERY DATASOURCE="#DataSource#" name="GetDates">
    SELECT WeekEndingDate
    FROM Productivity
    WHERE JobNumber = '#URL.JobNumber#' AND
    SubJobNumber = '#URL.SubJobNumber#' AND
    WeekEndingDate <= #dateadd("d",7,todaysDate)#
    </CFQUERY>


    <cfoutput query="GetDates">#DateFormat(WeekEndingDate, "medium")#<br></cfoutput>


    Aug 17, 2008
    Aug 24, 2008
    Aug 31, 2008
    Sep 7, 2008
    Sep 14, 2008
    Sep 21, 2008
    Sep 28, 2008
    Oct 5, 2008
    Oct 12, 2008
    Oct 19, 2008
    Oct 26, 2008

    I would think that I wouldn't see anything past the 24th. ??? Puzzling.
    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    August 18, 2008
    I think the offending line is WeekEndingDate <= #TodaysDate#-7
    . The arithmetic forces Coldfusion to cast Todaysdate from a date object to a numeric value. To see it, display this

    <cfset todaysDate = createODBCDate(Now())>
    <cfoutput>#todaysDate-7#</cfoutput>

    Then you will get something like 39671.9391667. Obviously, not what you wanted.

    To get a date in the future or in the past, use the dateAdd() function. For example,

    the date 7 years from now: dateadd("yyyy",7,todaysDate)
    the date 7 years ago: dateadd("yyyy",-7,todaysDate)
    the date this time next week: dateadd("d",7,todaysDate)



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