Link to home
Start Free TrialLog in
Avatar of bptacek
bptacek

asked on

Date

Whats the simplest way to filter my "date" column.  I want it to only query or filter the records based on the last 5 days.

Thanks
Ben

(all i have is 35 points or else I would give more)
Avatar of FranzRinkleff
FranzRinkleff

DateDiff
Description Returns the number of intervals, in units of type datepart, by which date1 is less than
date2.
Category Date and time functions
Syntax DateDiff(datepart, date1, date2)
See also DateAdd, DatePart, CreateTimeSpan
Parameters
Usage To find the number of days between date1 and date2, use Day of Year or Day.
When datepart is Weekday, DateDiff returns the number of weeks between the two
dates. If date1 falls on a Monday, DateDiff counts the number of Mondays to date2. It
counts date2 but not date1. If interval is Week, however, DateDiff returns the number
of calendar weeks between the two dates. It counts the number of Sundays between
date1 and date2. DateDiff counts date2 if it falls on a Sunday; but it doesn?t count
date1, even if it falls on a Sunday.
If Date1 refers to a later date than date2, DateDiff returns a negative number.
When passing date/time value as a string, enclose it in quotes. Otherwise, it is
interpreted as a number representation of a date/time object.
Year values 0 - 29 are interpreted as 21 st century dates. Year values 30 - 99 are
interpreted as 20 th century dates.
Example <!--- This example shows the use of DateDiff --->
<cfif IsDefined("FORM.date1") and IsDefined("FORM.date2")>
<cfif IsDate(FORM.date1) and IsDate(FORM.date2)>
Parameter Description
datepart One of the following strings:
?yyyy Year
?q Quarter
?m Month
?y Day of year
?d Day
?w Weekday
?ww Week
?h Hour
?n Minute
?s Second
date1 Date/time object in the range 100 AD?9999 AD
date2 Date/time object in the range 100 AD?9999 AD

DateCompare
Description Performs a full date/time comparison of two dates. Returns:
?-1 if date1 is less than date2
?0 if date1 is equal to date2
?1 if date1 is greater than date2
Category Date and time functions
Syntax DateCompare(date1, date2 [, datePart])
See also CreateDateTime, DatePart
Parameters
Usage When passing a date/time value as a string, enclose it in quotes. Otherwise, it is
interpreted as a number representation of a date/time object.
Year values 0 - 29 are interpreted as 21 st century dates. Year values 30 - 99 are
interpreted as 20 th century dates.
Example <!--- This example shows the use of datecompare --->
<html>
<head>
<title>DateCompare Example</title>
</head>
<basefont face = "Arial, Helvetica" size = 2>
<H3>DateCompare Example</H3>
<P>The datecompare function compares two date/time values.
<cfif IsDefined("FORM.date1")>
<cfif IsDate(FORM.date1) and IsDate(FORM.date2)>
Parameter Description
date1 Date/time object in the range 100 AD?9999 AD.
date2 Date/time object in the range 100 AD?9999 AD.
datePart Optional. The precision of the comparison. The parameter can
have any of the following values:
?s Precise to the second
?n Precise to the minute
?h Precise to the hour
?d Precise to the day
?m Precise to the month
?yyyy Precise to the year
By default, precision is to the second
using what DB?

This should work for most:
     <CFSET VARIABLES.currDate = Now()>
     <CFSET VARIABLES.lastFiveDays = CreateDate(Year(Now()), Month(Now),Day(Now)-5)>
     <CFQUERY NAME="dateBoundQuery" DATASOURCE="YOURDSN" DBTYPE="ODBC">
          select myField
          from myTable
          where someDateField < #CreateODBCDate(VARIABLES.currDate)#
          and someDateField > #CreateODBCDate(VARIABLES.lastFiveDays)#
     </CFQUERY>
Probably can just use the DateDiff function I submitted earlier.  But, you may find a way to use DateCompare.

For DateDiff

you could use datediff to filter the data after you query it so on the screen you only show the ones in the last 5 days.  

<cfif #DateDiff("d", "date1", "date2")# lte 5>
Or use the function DateAdd to only query for dates you want.  

<cfquery name="theQuery" datasource="#datasource#">
select * from theTable where date >= #DateAdd("d", 5, date1)#
</cfquery>

DateAdd
Description Returns a date to which a time interval has been added.
Category Date and time functions
Syntax DateAdd(datepart, number, date)
See also DateConvert, DatePart, CreateTimeSpan
Parameters
Usage The datepart specifiers "y," "d," and "w" perform the same function add a number
of days to a date.
When passing a date/time value as a string, enclose it in quotes. Otherwise, it is
interpreted as a number representation of a date/time object.
Parameter Description
datepart One of the following strings:
?yyyy Year
?q Quarter
?m Month
?y Day of year
?d Day
?w Weekday
?ww Week
?h Hour
?n Minute
?s Second
number Number of units of datepart to add to date (positive to get dates
in the future; negative to get dates in the past).
date Date/time object in the range 100 AD?9999 AD. Year values
0?29 are interpreted as 21 st century dates. Year values 30?99
are interpreted as 20 th century dates.

Avatar of bptacek

ASKER

cheekyci-

  That did not work.  It still pulls all the records.  I am using MySQL, and I do have the field as the "DATE" format.  I have tried the DATE_ADD function, and I can get it to work in the MySQL command prompt... but when I put it on the coldfusion query, I get all the records again.

Ben
what format is the DATE field.. is it string?

CJ
Avatar of bptacek

ASKER

no DATE format.  I think that is what you are asking... The Date format allows me "0000-00-00 00:00:00" for example.  Is that what you mean?  Its not VARCHAR, TEXT or anything like that.
<cfset theDate = "01/01/2001">
<cfquery name="theQuery" datasource="#datasource#">
  select * from theTable where date >= #DateAdd("d", -5,  theDate)#
</cfquery>

this should work. although you may have to change the query slightly so the CF date matches the date formato of your database.

for instance, the following query would look for dates 5 days previous to theDate.  

<cfset theDate = "01/01/2001">

<cfquery name="theQuery" datasource="#datasource#">
  select * from theTable where date >= trunc(to_date('#DateAdd("d", -5,  theDate)#', 'mm/dd/yyyy'))
</cfquery>
to format the date and time you can use the following function createODBCDateTime(date).  It will turn your date into something you can use when querying your databasel.  If you don't have the time do CreateODBCDate.  Or use my previous example trunc(to_date('#theDate#','mm/dd/yyyy'))  to search the database.  the trunc sets the time to 00:00:00

more info

CreateODBCDateTime
Description Returns a date/time object in ODBC timestamp format.
Category Date and time functions
Syntax CreateODBCDateTime(date)
See also CreateDateTime, CreateODBCDate, CreateODBCTime, Now
Parameters
Usage When passing a date/time value as a string, enclose it in quotes. Otherwise, it is
interpreted as a number representation of a date/time object.
Parameter Description
date Date/time object in the range 100 AD?9999 AD. Year values
0?29 are interpreted as 21 st century dates. Year values 30?99
are interpreted as 20 th century dates
try this:

    <CFSET VARIABLES.currDate = Now()>
    <CFSET VARIABLES.lastFiveDays = CreateDate(Year(Now()), Month(Now),Day(Now)-5)>
    <CFQUERY NAME="dateBoundQuery" DATASOURCE="YOURDSN" DBTYPE="ODBC">
         select myField
         from myTable
         where someDateField < #CreateODBCDateTime(VARIABLES.currDate)#
         and someDateField > #CreateODBCDateTime(VARIABLES.lastFiveDays)#
    </CFQUERY>

If that doesn't work .. copy and paste the above query into MySql and test to see what happens.

CJ
Avatar of bptacek

ASKER

Yours didn't work FranzRinkleff, I have tried something to that effect before too. Here is my error code to your's cheekyci:

Error Diagnostic Information

An error occurred while evaluating the expression:


 VARIABLES.lastFiveDays=CreateDate(Year(Now()),Month(Now),Day(Now)-5)



Error near line 3, column 7.
--------------------------------------------------------------------------------

Error resolving parameter NOW


ColdFusion was unable to determine the value of the parameter. This problem is very likely due to the fact that either:

You have misspelled the parameter name, or
You have not specified a QUERY attribute for a CFOUTPUT, CFMAIL, or CFTABLE tag.


The error occurred while processing an element with a general identifier of (CFSET), occupying document position (3:1) to (3:76).


Date/Time: Fri Sep 14 14:09:55 2001
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)

-----------------------------
when I make put the () on the "Now" it loads the page but no filter... but if I don't have those () on the Now, it gives me the error above.  

I tried it in MySQL, but no go.  Does MySQL support the CreateODBCDateTime funtion?  MySQL just gave me a Syntax error.
oops now is a function not a var:
   <CFSET VARIABLES.currDate = Now()>
   <CFSET VARIABLES.lastFiveDays = CreateDate(Year(Now()), Month(Now()),Day(Now())-5)>
   <CFQUERY NAME="dateBoundQuery" DATASOURCE="YOURDSN" DBTYPE="ODBC">
        select myField
        from myTable
        where someDateField < #CreateODBCDateTime(VARIABLES.currDate)#
        and someDateField > #CreateODBCDateTime(VARIABLES.lastFiveDays)#
   </CFQUERY>
bptacek,

Could you try this, it worked in ms sql. But i don't ve mysql, so didn't get to test whether it work or not.

Taking back of what cheekycj has written above, the variable that set the last 5 days. (Changed some of his code above too)

<CFSET currDate = Now()>
<CFSET lastFiveDays = CreateDate(Year(Now()), Month(Now()),Day(Now())-5)>
 
<CFQUERY NAME="dateBoundQuery" DATASOURCE="YOURDSN">
 select myField from myTable
 where someDateField <= CONVERT(DATETIME, #CreateODBCDateTime(lastFiveDays)#, 102)
</CFQUERY>

If the above query doesn't return the correct results, please try this:-

<CFQUERY NAME="dateBoundQuery" DATASOURCE="YOURDSN">
 select myField from myTable
 where someDateField < CONVERT(DATETIME, #CreateODBCDateTime(currDate)#, 102)
 and someDateField > CONVERT(DATETIME, #CreateODBCDateTime(lastFiveDays)#, 102)
</CFQUERY>

Good Luck.

sshhz
Avatar of bptacek

ASKER

Didn't work either way.  I will be deleting this question and finding a "mean-time" solution until I get more points.
Avatar of bptacek

ASKER

I couldn't find an answer to the problem, and didn't have enough points to make peoples troubleshooting worth it.
bptacek,

Did you try this link, where you can earn more FREE Question Points!

https://www.experts-exchange.com/jsp/memberEarnPoints.jsp

I'd know what does work on the codes that i supply to you, so that we can re-adjust the errors again.

sshhz
Avatar of bptacek

ASKER

yeah, i earned those points.  I am going to have to get on here and answer some when I get time.  THe error it gave me was right around the CONVERT portion.  I looked in my book to MySQL and couldn't find anything on a CONVERT command either, so I do know what the equivilent is.  I tried CONCATE but no go there either.  THanks though, I will repost it later and get it figured out.
bptacek,

I see. Thanks for the reply. Yup CONVERT portion should only work in MsSQL, perhaps you can get the mysql documentation on the syntax to do CONVERT.

Good Luck to you.

sshhz
Hi

U can do in two ways.
both in coldfusion or oracle.

like


<cfquery name="test" datasource="dsn">
 
  Select * from table
  where  DateDiff("d",date1,date2) <= 5

</cfquery>

or

<cfquery name="test" datasource="dsn">
 
  Select * from table
  where  ( sysdate() - datefields ) <=5

</cfquery>
Avatar of bptacek

ASKER

I have already deleted this entry, and I dont know how it popped back into active.  Sorry for those who answered after I already removed this entry.
ok.  rather than delete this question, please as community support to PAQ it for you.

CJ
Avatar of bptacek

ASKER

I have sent Community Support a request to PAQ this one.

Thanks
Ben
ASKER CERTIFIED SOLUTION
Avatar of Moondancer
Moondancer

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
How about if you set the query to find a range between date varibles that you set:
<cfset CreateStartDate = currDate = Now()>
<cfset CreateEndDate = CreateDate(Year(Now()), Month(Now()),Day(Now())-5)>
<CFQUERY NAME="getStats" Datasource="stats"> SELECT * FROM stats WHERE ((day >= #CreateStartDate#) AND (day <= #CreateEndDate#)) </CFQUERY>