[cfaussie] Re: Creating a Date Range
The other thing to consider is that while his current need might be to find the records for a specific month, if he hands the function a start date and an end date, that same code can be used for other intervals than just the current month - e.g. this quarter, last year, first half of the year etc. Taking that approach will make the function more likely to be useful for as-yet-undefined purposes. Experience has taught me that any time you define a function closely, and build it to meet the narrow requirements of that definition, Murphy's Law says someone will want to add a new use for the function as soon as you claim the job is 90% finished. (Unfortunately, the corollary is also often true - any time you provide for as-yet-unnamed future functionality, Sod's Law says it's one function that'll never be called for in the future. So you have to make your choices and take your chances.) Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month On Mon, Feb 23, 2009 at 11:15 AM, CyberAngel wrote: > > select count(*) as monthOpenTotal > from tbl_car > where dateraised between beginDate and endDate > > And set beginDate and endDate to date objects either in CF itself or use SQL > to do it. > > There is no need to do a select on the query in your example, just turn it > into a date object an you will be fine. > > > > -Original Message- > From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf > Of Gavin Baumanis > Sent: Monday, 23 February 2009 1:26 AM > To: cfaussie > Subject: [cfaussie] Creating a Date Range > > > Hi Everyone, > > Just a quick question to ask how you all create your date ranges? > For example I have a requirement to list all records where a datefield > is between the first and las day of THIS month. > > I use the following, but just wanted some verification, I suppose, > that there wasn't a better way to do it. > (Ms-Sql Server 2005) > > SET DATEFORMAT ymd >DECLARE @today DATETIME > >SET @today = CONVERT(CHAR(8), GETDATE(), 112) > >Select >count(*) as monthOpenTotal >from >tbl_car >where >dateRaised between >(SELECT DATEADD(DAY, > -DATEPART(DAY, @today) + 1, @today)) >and (SELECT DATEADD(DAY, > -DATEPART(DAY, @today), DATEADD(MONTH, > 1, @today)+ ' 23:59:59')) > > > Thanks for any ideas you might have. > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] [Ann-Syd] Tonight: Handons Cairngorm Basics
Meet on Monday 23rd Feb, 2009 at 6pm for 6:30 start. A hands on introduction the basics of developing applications using the Cairngorm Application development framework. We'll be walking through a provided sample application. Please bring along your notebook with Flex Builder or Eclipse/FlashDevelop and Flex SDK installed. (see http://groups.adobe.com/posts/d094878396 for links) Cairngorm is the lightweight micro-architecture for Rich Internet Applications built in Flex or AIR. A collaboration of recognized design patterns, Cairngorm exemplifies and encourages best-practices for RIA development advocated by Adobe Consulting, encourages best-practice leverage of the underlying Flex framework, while making it easier for medium to large teams of software engineers deliver medium to large scale, mission-critical Rich Internet Applications. Please RSVP (http://groups.adobe.com/posts/d094878396) if you want to get food and drinks provided courtesy of Adobe. Note: In order to be eligible for the major software prize, valued at $US2100, drawn at the June meeting you must have attended at least 3 meeting since the November major software prize draw. Previous major software winners within the last 2 years are ineligible. -- Chris -- Chris Velevitch Manager - Adobe Platform Users Group, Sydney m: 0415 469 095 www.apugs.org.au Adobe Platform Users Group, Sydney Feb '09 meeting: Hands On Cairngorm Basics Date: Mon 23rd Feb 6pm for 6:30 start Details and RSVP on http://groups.adobe.com/posts/d094878396 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Creating a Date Range
select count(*) as monthOpenTotal from tbl_car where dateraised between beginDate and endDate And set beginDate and endDate to date objects either in CF itself or use SQL to do it. There is no need to do a select on the query in your example, just turn it into a date object an you will be fine. -Original Message- From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf Of Gavin Baumanis Sent: Monday, 23 February 2009 1:26 AM To: cfaussie Subject: [cfaussie] Creating a Date Range Hi Everyone, Just a quick question to ask how you all create your date ranges? For example I have a requirement to list all records where a datefield is between the first and las day of THIS month. I use the following, but just wanted some verification, I suppose, that there wasn't a better way to do it. (Ms-Sql Server 2005) SET DATEFORMAT ymd DECLARE @today DATETIME SET @today = CONVERT(CHAR(8), GETDATE(), 112) Select count(*) as monthOpenTotal from tbl_car where dateRaised between (SELECT DATEADD(DAY, -DATEPART(DAY, @today) + 1, @today)) and (SELECT DATEADD(DAY, -DATEPART(DAY, @today), DATEADD(MONTH, 1, @today)+ ' 23:59:59')) Thanks for any ideas you might have. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Creating a Date Range
If you look at optimisation you wil find that doing the sub selects is faster as they only get performed once. using functions on the outer select can be more intensive. So I would suggest Gavin's way is ok. The only thing that might help is to make the date range outside the select as a local variable. Simon 2009/2/23 Kevan Stannard > > Perhaps you only need to consider the year and month? > > select count(*) as monthOpenTotal > from tbl_car > where >year(dateRaised) = year(getdate()) >and month(dateRaised) = month(getdate()) > > > > 2009/2/23 Gavin Baumanis : > > > > Hi Everyone, > > > > Just a quick question to ask how you all create your date ranges? > > For example I have a requirement to list all records where a datefield > > is between the first and las day of THIS month. > > > > I use the following, but just wanted some verification, I suppose, > > that there wasn't a better way to do it. > > (Ms-Sql Server 2005) > > > > SET DATEFORMAT ymd > >DECLARE @today DATETIME > > > >SET @today = CONVERT(CHAR(8), GETDATE(), 112) > > > >Select > >count(*) as monthOpenTotal > >from > >tbl_car > >where > >dateRaised between > >(SELECT > DATEADD(DAY, -DATEPART(DAY, @today) + 1, @today)) > >and (SELECT DATEADD(DAY, > -DATEPART(DAY, @today), DATEADD(MONTH, > > 1, @today)+ ' 23:59:59')) > > > > > > Thanks for any ideas you might have. > > > > > > > > > > > -- Cheers Simon Haddon Woman loves feeling danger and speed. That is why woman wants man. They get a speed rush that is the most dangerous of all. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Creating a Date Range
Perhaps you only need to consider the year and month? select count(*) as monthOpenTotal from tbl_car where year(dateRaised) = year(getdate()) and month(dateRaised) = month(getdate()) 2009/2/23 Gavin Baumanis : > > Hi Everyone, > > Just a quick question to ask how you all create your date ranges? > For example I have a requirement to list all records where a datefield > is between the first and las day of THIS month. > > I use the following, but just wanted some verification, I suppose, > that there wasn't a better way to do it. > (Ms-Sql Server 2005) > > SET DATEFORMAT ymd >DECLARE @today DATETIME > >SET @today = CONVERT(CHAR(8), GETDATE(), 112) > >Select >count(*) as monthOpenTotal >from >tbl_car >where >dateRaised between >(SELECT DATEADD(DAY, > -DATEPART(DAY, @today) + 1, @today)) >and (SELECT DATEADD(DAY, > -DATEPART(DAY, @today), DATEADD(MONTH, > 1, @today)+ ' 23:59:59')) > > > Thanks for any ideas you might have. > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Creating a Date Range
Hi Everyone, Just a quick question to ask how you all create your date ranges? For example I have a requirement to list all records where a datefield is between the first and las day of THIS month. I use the following, but just wanted some verification, I suppose, that there wasn't a better way to do it. (Ms-Sql Server 2005) SET DATEFORMAT ymd DECLARE @today DATETIME SET @today = CONVERT(CHAR(8), GETDATE(), 112) Select count(*) as monthOpenTotal from tbl_car where dateRaised between (SELECT DATEADD(DAY, -DATEPART(DAY, @today) + 1, @today)) and (SELECT DATEADD(DAY, -DATEPART(DAY, @today), DATEADD(MONTH, 1, @today)+ ' 23:59:59')) Thanks for any ideas you might have. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
