MySQL Search Date Field - Fails

[MichaelCarroll]

Ok

I have a date field connected to a DATETIME field in MySQL. If I look at the raw unfiltered value it looks like this:

10/02/2010 12:00:00 00 am, 10/04/2008 12:00:00 00 am, ...

So I want to search on this and create a search field. I set search style to 2 or 'is contained in'.

I type in 2008 or 2010 and it does not work.

I can search on any of my other fields in this table and it works as advertised, but on a date filed I am stumped.

Any ideas on what I am missing here.

Thanks,

Michael Carroll

www.redrocksbluesky.com
www.theminimalistrunner.com
[email protected]
435-275-5170

[MichaelCarroll]

Re: MySQL Search Date Field - Fails

If you were going to do this via SQL you would say:

SELECT id_results, date
FROM results
WHERE year(date) = 2010

So I am wondering out loud how this would be done in the grid search part?

Thanks...

[MichaelCarroll]

Re: MySQL Search Date Field - Fails

Ok - this is my work around. Add a new calculated field to the MySQL view that extracts out the year value as an INT(4) then add it as a hidden field on the grid. Then Search on it.

Thanks,

[asawyer13]

Re: MySQL Search Date Field - Fails

Michael,
FYI, if you execute this sql statement:

SELECT id_results, date
FROM results
WHERE year(date) = 2010

it will not use an index on the date if you happen to have one.
If you said,

SELECT id_results, date
FROM results
WHERE date like '2010%'

then it would use the index and would be faster.

Info to tuck away in case you ever need it.
Alan

[Pat Bremkamp]

Re: MySQL Search Date Field - Fails

Michael,

This is not the answer to your question, but I think it's kind of interesting, and maybe something in here will spark an idea.

I have a client whose customers are not the shaprest tacks in the knife drawer, so we needed an easy way to filter data on dates. We wanted one month of dates to show, so we came up with a dropdown in the search as shown in the pic.

The dropdown choices are in field rd_date and are set up as follows:

 
Current Month|current
[[[ 2009 ]]]|[[[ 2009 ]]]
January 2009|0109
February 2009|0209
March 2009|0309
April 2009|0409
May 2009|0509
June 2009|0609
July 2009|0709
August 2009|0809

and so on.

Then in the "OnSearchPartFilterCompute" we have this code

 
function  OnSearchPartFilterCompute as v (e as p)
vChoice=e.SearchDataSubmitted.rd_date
' date is character in the format mmyy
if vChoice="current"
 vChoice=str(month(date()),2,0)+right(str(year(date()),4,0),2)
 vChoiceFirst=cdate(date_firstdayofmonth(date()))
 vChoiceLast=cdate(date_lastdayofmonth(date()))
else  'get chosen month
 vChoiceFirst="20"+right(vChoice,2)+"-"+left(vChoice,2)+"-"+"01"
 vChoiceLast = cdate(date_lastdayofmonth(ctod(left(vChoice,2)+"-"+"01-"+right(vChoice,2))))
end if
dim cnx as SQL::Connection
dim args as SQL::Arguments
dim crs as SQL::ResultSet
cnx.open("::name::sir")
cnx.execute("SELECT count(*) AS kount FROM readings WHERE  rd_date>='"+vChoiceFirst+"' AND rd_date<='"+vChoiceLast+"'", args)
crs=cnx.ResultSet
vCount=crs.data("kount")
if vCount=0  'need to add month worth of records
 cnx.execute("call add_a_month_to_site(:argSite,:argChoice)", args)
end if
cnx.close()
e.rtc.Filter_SearchPart="rd_date >= '"+vChoiceFirst+"' AND rd_date <= '"+vChoiceLast+"'"
end function

As you can see, we also added a check to see if there were any dates, and if not, run a stored procedure to create them before applying the filter...works great (and fast!)

Pat

[MichaelCarroll]

Re: MySQL Search Date Field - Fails

Well for not answering my question -- you answered my question in far greater detail than I thought I would get. Also, thanks for the tip Alan it is appreciated.

Thank you.

Michael Carroll

[Jim Coltz]

Re: MySQL Search Date Field - Fails

As long as you have the "Allow 'Query by Form' Syntax" box checked in the search properties you can use this

1/31/1959..12/31/1959

to search for all dates in the year 1959.