|
Matching date ranges to flex arrays in SQL queries |
||||||||
Whilst writing a search function for a flex applicaiton I needed to be able to filter results back to flex from a remote cfc by a variety of criteria. Two of these were the month, and the year. So here's a handy way of doing it.
In flex I have an array of months "0 to 11", and a list of years, as an example "2005,2006,2007,2008".
So if I send those two data types back to a cfc, how can I filter my results based on them?
SQL has an in built function to do almost exactly this.
1SELECT fields
2FROM table
3WHERE Month(fieldName)=Month(Now())
4AND Year(fieldName)=Year(Now())
2FROM table
3WHERE Month(fieldName)=Month(Now())
4AND Year(fieldName)=Year(Now())
This will match records based on the month and year right now, in my case I just substituted this function for my passed argument, plus a small tweak, as my flex month array was 0 to 11, and coldfusion month index is 1 to 12 based.
1SELECT fields
2FROM table
3WHERE Month(fieldName)=Month(arguments.month)
4AND Year(fieldName)=Year(arguments.year)
2FROM table
3WHERE Month(fieldName)=Month(arguments.month)
4AND Year(fieldName)=Year(arguments.year)
TweetBacks
There are no comments for this entry.
[Add Comment] [Subscribe to Comments]