On Thu, Aug 22, 2013 at 9:24 AM, Dave Crozier <[email protected]> wrote:
> Andy, > The date field in SQL is to be deprecated and all dates should be held in > DateTime format. That can sometimes produce a real problem where the SQL > "Between" function is used as the default datetime for a date alone returns > 00:00:00 as hours mins & seconds. so if you do a between using datetime > values which have a defaulted hh:mm:ss as 0 then the end data DOESN'T > include the whole of the end date. You need to force in 23:59:59 as the > hh:mm:ss value. Either that or do a strictly "<" the end date+1. This > really bit me a while ago in a big way where loads of sales were made on > the last day of the month which wasn't included in my SQL extract.... > > Result: big red face for yours truly once I found the error! > > ------------------- Till you make a UDF in the db to "fix" a date and call that function to clean a date before you use it in a query. declare @midnight datetime select @midnight = dateadd(d, datediff(d, 0, getdate()), 0) print @midnight -- Stephen Russell Sr. Analyst Ring Container Technology Oakland TN 901.246-0159 cell --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/cajidmyjvefehidjuaerlwmtit6zc4zmaqbfyejxeyyzx0xj...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
