Solved

Date

Posted on 2001-09-14
24
319 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
  • 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

708 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

12 Experts available now in Live!

Get 1:1 Help Now