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 -~----------~----~----~----~------~----~------~--~---
