Solved

Date

Posted on 2001-09-14
24
333 Views
Last Modified: 2013-12-24
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)
0
Comment
Question by:bptacek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 5
  • +4
24 Comments
 
LVL 1

Expert Comment

by:FranzRinkleff
ID: 6483000
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
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6483009
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>
0
 
LVL 1

Expert Comment

by:FranzRinkleff
ID: 6483019
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>
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 1

Expert Comment

by:FranzRinkleff
ID: 6483048
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.

0
 

Author Comment

by:bptacek
ID: 6483194
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
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6483221
what format is the DATE field.. is it string?

CJ
0
 

Author Comment

by:bptacek
ID: 6483240
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.
0
 
LVL 1

Expert Comment

by:FranzRinkleff
ID: 6483254
<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>
0
 
LVL 1

Expert Comment

by:FranzRinkleff
ID: 6483322
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
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6483359
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
0
 

Author Comment

by:bptacek
ID: 6483581
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.
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6485149
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>
0
 
LVL 2

Expert Comment

by:sshhz
ID: 6486452
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
0
 

Author Comment

by:bptacek
ID: 6487750
Didn't work either way.  I will be deleting this question and finding a "mean-time" solution until I get more points.
0
 

Author Comment

by:bptacek
ID: 6487752
I couldn't find an answer to the problem, and didn't have enough points to make peoples troubleshooting worth it.
0
 
LVL 2

Expert Comment

by:sshhz
ID: 6489251
bptacek,

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

http://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
0
 

Author Comment

by:bptacek
ID: 6490385
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.
0
 
LVL 2

Expert Comment

by:sshhz
ID: 6492239
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
0
 
LVL 6

Expert Comment

by:dash420
ID: 6651988
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>
0
 

Author Comment

by:bptacek
ID: 6652707
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.
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6659751
ok.  rather than delete this question, please as community support to PAQ it for you.

CJ
0
 

Author Comment

by:bptacek
ID: 6659850
I have sent Community Support a request to PAQ this one.

Thanks
Ben
0
 
LVL 1

Accepted Solution

by:
Moondancer earned 0 total points
ID: 6659864
Points refunded, moving at zero points to PAQ.
Moondancer
Community Support Moderator @ Experts Exchange
0
 

Expert Comment

by:quanta
ID: 7320092
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>
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

687 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