InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 02:26:30 GMT20http://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxApologies for the delayed response. Thank you for the feedback and for posting the function you built. It might help others having the same problem.Sun, 22 Apr 2012 10:28:58 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxGreat function to get the weekdays. I had a need to calculate hour differenct between two datetimes excluding weekends (exclude 48 hours for each week end) to calculate age of a document to be mailed disregaring holidays (as holidays are not so important compared with complexity it will involve and given the context of the document, according to my team lead)I managed to change a little bit of your function for my need. Thanks for the idea you gave in your article. saved my day./****** Object: UserDefinedFunction [dbo].[fn_HourDiffExcudeWeekend] Script Date: 04/04/2012 03:08:57 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Description: Calculate the hour gap between two given datetimes -- excluding weekend 48 hours (from Saturday 00:00 hours to Sunday 24:00 hours)-- =============================================CREATE FUNCTION [dbo].[fn_HourDiffExcudeWeekend]( @StartDate datetime, @EndDate datetime)RETURNS intASBEGIN DECLARE @HourGap int --Temporarily holds @EndDate during date reversal DECLARE @Swap DATETIME --===== If the inputs are in the wrong order, reverse them IF @StartDate > @EndDate BEGIN SELECT @Swap = @EndDate, @EndDate = @StartDate, @StartDate = @Swap END --====== Calculate hour gap excluding weekend 48 hours SELECT @HourGap = -- hour difference betwenn the two days (DATEDIFF(hh, @StartDate, @EndDate)) -- substract 48 hours for each weekend --The DATEPART(dw,date) does not actually count weeks... It counts the -- transition to a Sunday regardless of the DATEFIRST setting. In essence, it counts -- only whole weekends in any given date range. -(DATEDIFF(wk, @StartDate, @EndDate) * 2 * 24) -- Substract the additional hours added by the @StartDate of Sunday to get the hour diff from starting Next Monday 00:00 Hours -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN (24 - DATEDIFF(hh,CAST(@StartDate as date), @StartDate) ) ELSE 0 END) -- Substract the additional hours added by the @EndDate of Saturday to get the hour diff to @EndDate 00:00 Hours (Prevoius Friday 24:00 hours) -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN DATEDIFF(hh,CAST(@EndDate as date), @EndDate) ELSE 0 END) -- Add hours from @StartDate 00:00 to the @StartDate time (xx:00 hours), to adjust the efect(substracting 48 more hours) of counting additional 1 weekend in --> -(DATEDIFF(wk, @StartDate, @EndDate) * 2 * 24) +(CASE WHEN DATENAME(dw, @StartDate) = 'Saturday' THEN DATEDIFF(hh,CAST(@StartDate as date), @StartDate) ELSE 0 END) -- Add hours from @EndDate time xx:00 hours to @EndDate 24:00 hours to adjust the efect(substracting 48 more hours) of counting additional 1 weekend in --> -(DATEDIFF(wk, @StartDate, @EndDate) * 2 * 24) +(CASE WHEN DATENAME(dw, @EndDate) = 'Sunday' THEN (24 -DATEDIFF(hh,CAST(@EndDate as date), @EndDate)) ELSE 0 END) RETURN @HourGapENDGOWed, 04 Apr 2012 01:25:28 GMTchaminda_chandrasekarahttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspx[quote][b]rick.myers (2/6/2012)[/b][hr]This functions doesn't actually work. Just try Jan 1, 2012 to Jan 31, 2012. Or February 2012.[/quote]Since you tried it, how about sharing what you're getting for results and why you think it's not working.Mon, 06 Feb 2012 16:16:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxThis functions doesn't actually work. Just try Jan 1, 2012 to Jan 31, 2012. Or February 2012.Mon, 06 Feb 2012 12:54:25 GMTrick.myershttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspx[quote][b]Jeff Moden (2/5/2010)[/b][hr]I saw a tight little forumula for this about a year ago... I'm not sure where I put it but I'll try to find it this weekend.[/quote]Ah... I remember where I saw it... Ironically, it was in this very discussion thread![url]http://www.sqlservercentral.com/Forums/Topic153606-203-3.aspx#BM591629[/url]There's also another article for various functions of this nature. One of them is a function to look ahead or look back. Please test them before you use them because I have not.[url]http://www.mssqlcity.com/Articles/UDF/DateTimeUDF.htm#part_2_4[/url]Obviously, they could use a bit of optimizing but it's betterr than trying to "correct a blank piece of paper". :-)Mon, 08 Feb 2010 19:14:26 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxI saw a tight little forumula for this about a year ago... I'm not sure where I put it but I'll try to find it this weekend.Fri, 05 Feb 2010 09:17:19 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxSaw your update after you posted mine. The problem with populating a table is you don't know how many days to populate it with. It seems you would have to populate an arbitrary number of days that will guarantee to be at least enough to cover weekends, then count.It seems like its probably more efficient to just loop through adding days and counting them when its a weekday.I'm wondering if there is a formula you can use to account for the weekends and just add the days and subtract weekdays. The more I think about it, the more complications I see with the start date and end date spanning weekends though.Fri, 05 Feb 2010 09:13:53 GMTscottsimpsonhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxThat doesn't work, it gives the same result if you add a day or weekday.These two sets of statements return the same result.select dateadd(weekday,1, getdate())select dateadd(weekday,2, getdate())select dateadd(weekday,3, getdate())select dateadd(weekday,4, getdate())select dateadd(weekday,5, getdate())select dateadd(weekday,6, getdate())select dateadd(weekday,7, getdate())select dateadd(weekday,8, getdate())select dateadd(day,1, getdate())select dateadd(day,2, getdate())select dateadd(day,3, getdate())select dateadd(day,4, getdate())select dateadd(day,5, getdate())select dateadd(day,6, getdate())select dateadd(day,7, getdate())select dateadd(day,8, getdate())Fri, 05 Feb 2010 09:10:08 GMTscottsimpsonhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspx[quote][b]scottsimpson (2/4/2010)[/b][hr]...I don't see a clean way of doing this without doing a while loop counting the days as I go.[/quote]Yes, use the DATEADD function, see: [url]http://msdn.microsoft.com/en-us/library/ms186819.aspx[/url]Edit: Sorry, misread your question. Missed the important word 'weekdays'.As suggested above use a Date Table or Calendar table.Fri, 05 Feb 2010 08:25:35 GMTnigel.http://www.sqlservercentral.com/Forums/Topic153606-203-1.aspx[quote][b]Robert Stewart (1/5/2005)[/b][hr]Actually, if you are willing to use a "Date Table," make it a date dimension table. Do all of the date calculations that can be done to a date ahead of time, calculate the day of week, week of year, day of month, month name and number, etc. You can add a column for something like "HolidayFlag" and another one for "WeekendFlag." Now, all you have to do is go to this table with the beginning and ending dates and count the number of No's in the weekend flag and subtract the number of Yes's in the Holiday flag for the date range you want to check.[/quote]I agree with R.Stewart. I've used this type of approach in the past very successfully, especially in ETLs or DW applications.Fri, 05 Feb 2010 01:18:45 GMTsubscriptions-524733http://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxI agree with the principle of avoiding cursors and row based operations. However I'm trying to do this same type of calculation, but given a start date, add a number of weekdays and return the date. I don't see a clean way of doing this without doing a while loop counting the days as I go.Anyone got any idea of how I could avoid thisfunction would be something likeGetEndDate(@StartDate datetime, @Weekdays int, @EndDate datetime OUT)Thu, 04 Feb 2010 13:43:23 GMTscottsimpsonhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxSorry I didn't have an answer for your previous question, Mac. I sure do appreciate you posting the URL that sorted it for you, though. Thanks for the great feedback.Tue, 17 Nov 2009 23:18:27 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxI've actually found this article http://www.sqlservercentral.com/articles/T-SQL/65423/ to be really helpful as well.I added the criteria[code="sql"]in (SELECT HolidayDate FROM DbHolidays WHERE Location = @LocationId)))[/code]and [code="sql"] @LocationId int[/code]to that on this page.And this allows me to have customised dates for various Locations included in the holiday calculation.In this way, if one region has a 'region only' public holiday, this can be included in the calculation.Two really good articles that have helped me immenselyTue, 17 Nov 2009 18:38:11 GMTOceanDiscoveryhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxI've added both the UDF's udfHolidayDays, and addBusinessDays, as well as the table tblSysHolidaysThe table has three dates.If I query the udfHolidayDays as[code="sql"]SELECT [dbo].[udfHolidayDays] ( '2009-Nov-17' ,'2009-Dec-31')SELECT [SysHolidayID] ,[HolidayDate] ,[Description] FROM [dbo].[tblSysHolidays][/code]I get-----------3(1 row(s) affected)SysHolidayID HolidayDate Description------------ ----------------------- ----------------------------------------1 2009-12-02 00:00:00.000 Test December 2nd2 2009-12-25 00:00:00.000 Christmas Day3 2009-12-26 00:00:00.000 Boxing Day(3 row(s) affected)However, when I run [code="sql"]SELECT [dbo].[addBusinessDays] ('20091117', 12)[/code]I get -----------------------2009-11-29 00:00:00.000(1 row(s) affected)But by my reckoning it should be December 4thWhat have I done wrong??Mon, 16 Nov 2009 20:42:35 GMTOceanDiscoveryhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxI calculate wokringdays like this:SELECT days/7*5 + days%7 - CASE WHEN 6 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END - CASE WHEN 7 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 ENDFROM (SELECT DATEDIFF(day, @StartDate, @EndDate) + 1 AS days, DATEPART(weekday, @StartDate + @@DATEFIRST - 1) AS wd ) AS D)Tue, 06 Oct 2009 04:48:11 GMTwim.buyenshttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspx[quote][b]Lynn Pettis (2/19/2009)[/b][hr]I don't think our PeopleSoft Admin has gotten around to this, so I thought I'd drop a line. She found your routine quite elegant and saved her a lot of work on a project she initiated to help HR/Payroll with a process which is currently very manual intensive.[/quote]I don't know how I missed this, Lynn. Thanks for the feedback.{EDIT} Wow! Deju vu, huh? More coffee, please.Mon, 04 May 2009 11:11:36 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspx[quote][b]James A. Lawrence (5/4/2009)[/b][hr]Just tried this out....good job!Or in SoCal parlance: "Dude, you rock!":cool:[/quote]Great to hear, James. Thanks for taking the time to post a reply. :-)Mon, 04 May 2009 11:10:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxJust tried this out....good job!Or in SoCal parlance: "Dude, you rock!":cool:Mon, 04 May 2009 09:31:37 GMTdbajuniorhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspx[quote][b]bindu.muttavarapu (3/6/2009)[/b][hr]Hi,Can any one help in calculating wordays starting from monday to fridayright now I am using (DATEDIFF(DAY, DATES.StartDate, DATES.EndDate)+1) to get monday to sat but what to do to get from monday to friday ..[/quote]I believe you may have missed the article this thread is attached to... please see the following...[url]http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/[/url]Fri, 06 Mar 2009 15:24:02 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxHi,Can any one help in calculating wordays starting from monday to fridayright now I am using (DATEDIFF(DAY, DATES.StartDate, DATES.EndDate)+1) to get monday to sat but what to do to get from monday to friday ..Fri, 06 Mar 2009 15:04:05 GMTbindu.muttavarapuhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspx[quote][b]Lynn Pettis (2/19/2009)[/b][hr]I don't think our PeopleSoft Admin has gotten around to this, so I thought I'd drop a line. She found your routine quite elegant and saved her a lot of work on a project she initiated to help HR/Payroll with a process which is currently very manual intensive.[/quote]Dang... sorry I missed this. Thanks for the feedback, Lynn.Mon, 02 Mar 2009 20:44:20 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxI don't think our PeopleSoft Admin has gotten around to this, so I thought I'd drop a line. She found your routine quite elegant and saved her a lot of work on a project she initiated to help HR/Payroll with a process which is currently very manual intensive.Thu, 19 Feb 2009 09:26:27 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxThanks for the feedback, Karen. I know there's a lot of them, but some of the posts in the discussions in this thread show some pretty nifty ways to handle the problem, as well.Thu, 19 Feb 2009 08:51:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxExcellent post Jeff. I had written a function to calculate business days about 3 1/2 years ago for a client of mine (http://www.sqlservercentral.com/scripts/Miscellaneous/31543/). Yours is a bit more elegant. I had to have a variable work week parameter and include holidays that were stored in a holiday table. I couldn't think of a way at the time to get an accurate answer for every scenario other than the solution that I implemented. I may have to refactor my original to be non-RBAR using the technique that you demonstrated. Thu, 19 Feb 2009 04:25:14 GMTkgaydahttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxIt was really fantastic thank you so much....Wed, 18 Feb 2009 03:40:05 GMTMadhu Bhhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspx[quote][b]Ramesh Lende (1/10/2006)[/b][hr]I know everyone is talking about Holidays For this particular function. but guys don't worry to consider holidays in this is very easy. I have modified this SQL little bit and it would take care of holidays also, even though holiday comes on weekend. First Create Holiday Tablecreate table HolidayTable (HolDay datetime)Insert two Holidays in it. (One on weekday and one on weekend just tomake sure function does consider weekend holidays)insert into HolidayTable values ('01/03/2006')insert into HolidayTable values ('01/08/2006')Now change SQL to SELECT (DATEDIFF(dd, @StartDate, @EndDate) + 1) -(DATEDIFF(wk, @StartDate, @EndDate) * 2) -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) - ( Select count(*) from HolidayTable where HolDay between @StartDate and @EndDate and (datepart(dw,HolDay) <> 1 and datepart(dw,HolDay)<>7 ))and you are ALL SET. Hope this helps, Best Regards,Ramesh. [/quote]Wed, 18 Feb 2009 03:39:20 GMTMadhu Bhhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxThese days, with furloughs a popular tool for profitability, a flexible approach is necessary.Holidays have to be managed by the company, not just by logic. I think a two-step process is in order.Start by importing from a web service or other source the holidays your company observes. http://www.holidaywebservice.com/holidays/HolidayService.asmx might be suitable. Do it for a 50- or 100-year period, whatever suits. Edit these if necessary. This gives you a start. Then, row by agonizing row, add the special holidays or furloughs your company has observed and plans to observe. Or, if you plan to offload this to somebody in HR, create an interface. Keep the list in a table or in an xml file.Whichever way you do your calculations, consult this list. Fri, 02 Jan 2009 07:10:57 GMTJay Taylor-604520http://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxJeff,Glad to hear that you are getting some use out of it. It get's used about 30-40 times a day to update work tickets in our system.Me personally, I would just do a lookup on the Calendar table to find out if the day is a Holiday. If performance was bad, I would try a non-clustered index and see if that helps at all. (year, month, day, holiday). Could actually do some logic to verify you are actually in a month with a holiday (no holiday in June).FraggleWed, 31 Dec 2008 23:58:33 GMTFraggle-805517http://www.sqlservercentral.com/Forums/Topic153606-203-1.aspx[quote][b]Fraggle (10/24/2008)[/b][hr]Jeff,Alright, now that I have had a little time to ponder the imponderable as my dad would say, I figured out how to add X number of business days to a start date.[code]Create Function fn_AddBusinessDays(@StartDate DATETIME,@DaysToAdd TINYINT)Returns DATETIMEASBEGINDECLARE @EndDate DATETIMESET @EndDate = DateAdd(Week, @DaysToAdd/5, @StartDate) + CASE WHEN DatePart(dw, @StartDate) + @DaysToAdd % 5 >= 7 THEN @DaysToAdd % 5 + 2 ELSE @DaysToAdd % 5 --else justadd the day ENDRETURN CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN DateAdd(dd, 2, @EndDate) WHEN DATENAME(dw, @EndDate) = 'Sunday' THEN DATEADD(dd,1,@EndDate) ELSE @EndDateEndEND[/code]See what a little time will do. No RBAR! Fraggle[/quote]Hey there, Fraggle... I know it's been a while, but I thought that I'd tell you that not only did I test your good function, but I've used it quite a few times. The cool thing about it is that you can easily use it on a whole bunch of dates. When you trying to do the same thing with a calendar table, you have to do a subquery with something like ...SELECT MIN(calen_dt) FROM dbo.Calender WHERE calen_dt >= DATEADD(d, @intErval, @dteCurrentDate) AND flg_bdate='Y' Now, I'll admit that your function doesn't handle holiday's like a calendar table might... but if you had a nice holiday table... Anyway, thanks again for the function.Wed, 31 Dec 2008 19:03:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspx[quote][b]aevans1981 (11/4/2008)[/b][hr]First, thanks for a great function, however...I have read through most of this post, but not all, I am using the code that was originally supplied.When I try fn_WorkDays(27/10/2008 00:00:00, 27/10/2008 00:00:00) it returns 1, shouldnt this be 0?[/quote]If you leave for a business trip on 27/10/2008 and return on 27/10/2008 - how many days you spend on business trip?Ask your accounting department, see what they will say.Thu, 06 Nov 2008 12:57:22 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspx[quote][b]aevans1981 (11/5/2008)[/b][hr]Thanks for that Jeff, that worked great.I have commented out the swap section as i am using this to calculate the time taken to turn around orders from when we receive them, which may result in negative figures.I am now getting wrong days returned when usingfn_WorkDays(ReceiveOrder, OrderRequired)fn_WorkDays(27/10/2008 00:00:00, 23/10/2008 00:00:00)returns -2, shouldnt this be -3, they have given us -3 working days to supply this order?[/quote]I believe the easist thing to do would be to put the swap section back in with a "caveat chaser"... start a variable at "1"... if a swap occurs, change it to a -1. Either way, multiply the number of days times that variable.Wed, 05 Nov 2008 18:01:43 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxThanks for that Jeff, that worked great.I have commented out the swap section as i am using this to calculate the time taken to turn around orders from when we receive them, which may result in negative figures.I am now getting wrong days returned when usingfn_WorkDays(ReceiveOrder, OrderRequired)fn_WorkDays(27/10/2008 00:00:00, 23/10/2008 00:00:00)returns -2, shouldnt this be -3, they have given us -3 working days to supply this order?Wed, 05 Nov 2008 01:46:17 GMTaevans1981http://www.sqlservercentral.com/Forums/Topic153606-203-1.aspx[quote][b]aevans1981 (11/4/2008)[/b][hr]First, thanks for a great function, however...I have read through most of this post, but not all, I am using the code that was originally supplied.When I try fn_WorkDays(27/10/2008 00:00:00, 27/10/2008 00:00:00) it returns 1, shouldnt this be 0?[/quote]It's mostly a personal or business requirements choice... if your requirements are that today only is a full day, then it should return a 1. If your requirements are that today should not be counted, then it should return a 0. The fix to do that is quite simple (and could easily be parameterized)... just change the following...[code] --Start with total number of days including weekends (DATEDIFF(dd,@StartDate,@EndDate)[font="Arial Black"]+1[/font])[/code]... to...[code]SELECT --Start with total number of days including weekends (DATEDIFF(dd,@StartDate,@EndDate))[/code]Tue, 04 Nov 2008 19:16:18 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxFirst, thanks for a great function, however...I have read through most of this post, but not all, I am using the code that was originally supplied.When I try fn_WorkDays(27/10/2008 00:00:00, 27/10/2008 00:00:00) it returns 1, shouldnt this be 0?Tue, 04 Nov 2008 08:39:55 GMTaevans1981http://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxNo fruity loops in my database please. :)Fri, 24 Oct 2008 20:06:00 GMTFraggle-805517http://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxHeh... thanks Fraggle... I guess you really do hate RBAR after all! :)Fri, 24 Oct 2008 19:53:56 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxJeff,Alright, now that I have had a little time to ponder the imponderable as my dad would say, I figured out how to add X number of business days to a start date.[code]Create Function fn_AddBusinessDays(@StartDate DATETIME,@DaysToAdd TINYINT)Returns DATETIMEASBEGINDECLARE @EndDate DATETIMESET @EndDate = DateAdd(Week, @DaysToAdd/5, @StartDate) + CASE WHEN DatePart(dw, @StartDate) + @DaysToAdd % 5 >= 7 THEN @DaysToAdd % 5 + 2 ELSE @DaysToAdd % 5 --else justadd the day ENDRETURN CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN DateAdd(dd, 2, @EndDate) WHEN DATENAME(dw, @EndDate) = 'Sunday' THEN DATEADD(dd,1,@EndDate) ELSE @EndDateEndEND[/code]See what a little time will do. No RBAR! FraggleFri, 24 Oct 2008 19:10:42 GMTFraggle-805517http://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxHi Jim,Thanks for the feedback. It's amazing, to me, how many posts this article elicited... and it was my very first article, to boot! :)As you probably already know, the following are equivalent...c BETWEEN a AND bc >= a AND c <= bNo big suprises and, as you say, it's just a matter of syntactical preference... except when it comes to things like dates. I've seen all sorts of gyrations with dates when trying to include whole dayswith times including the incredulous... b BETWEEN a AND a +23:59:59.999... which, of course and due to rounding, will NOT give the desired results. Even the more proper but still subject to error... b BETWEEN a AND a +23:59:59.997... is less effective than the correct alternative and, depending on the datatype, could still produce incorrect results in 2008.The correct way to look for a single whole day on a datetime column is...b >= a AND b < a+1Of course, that's only if "a" is a datetime with a midnight value or what people call a whole date or "date with no time". You should see some of the gyrations people go through to make that happen to "a".Bottom line is that using BETWEEN on DateTime datatypes might mean you accidently include many of the entries on the next day or you miss many of the entries on the current day.Fri, 24 Oct 2008 18:39:46 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxJeff -- I noticed your:"Yup, I know about BETWEEN… that would be the subject of whole different article."and wondered if you ever addressed that more. (I'll admit I didn't get through all 12 pages of the comments to your post.)I have always assumed (perhaps incorrectly) that the difference was simply syntax, and now I wonder if I should pay more attention.(As usual, I stand in awe of the contributions you make here. Thanks!)Fri, 24 Oct 2008 06:05:52 GMTJim Russell-390299http://www.sqlservercentral.com/Forums/Topic153606-203-1.aspxHeh... dang managers all need to take a course in how to effectively plan and schedule a software project for success. ;)Anyway, glad it does what you want and the performance isn't too bad.Thu, 23 Oct 2008 19:17:34 GMTJeff Moden