Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 155, comments - 2679, trackbacks - 64

My Links



Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.



Post Categories




July 2008 Blog Posts

How To Calculate the Number of Week Days Between two Dates

If the start date and end date are both week days, then the total number of week days in between is simply: (total difference in days) - (total difference in weeks) * 2 or  DateDiff(dd, @start, @end) - DateDiff(ww, @start, @end)*2 ... since the DateDiff() function with weeks returns the number of week "boundaries" that are crossed; i.e., the number of weekends. If you have a table of holidays, then you can simply subtract them out as well: DateDiff(dd, @start, @end) - DateDiff(ww, @start, @end)*2 -  (select count(*) from holidays where holiday_date between @start and @end) Now, what if the start day or the end day is...

posted @ Thursday, July 31, 2008 12:39 PM | Feedback (13) | Filed Under [ T-SQL ]

Convert input explicitly at your client; don't rely on the database to "figure it out"

A common mistake beginners make when working with SQL is trying to format their output at the database layer, as opposed to simply doing this at the presentation layer (i.e., client application, reporting tool, web page, etc).  I've covered that quite a bit in various blog posts, but I've only touched upon another similar issue which I feel is equally as important and also commonly mishandled. In the SqlTeam forums, I often see code that accepts DateTime input in the form of a string value (say, from a TextBox on a web form) and uploads that value to the database written...

posted @ Thursday, July 24, 2008 9:12 AM | Feedback (11) | Filed Under [ T-SQL .NET (C# / VB) Techniques DateTime Data ]

The MailBag --- Super-Sized Edition! String Parsing, Crosstabs, SQL Injection, and more.

OK, boys and girls, it's time for the mailbag!  There's lots of stuff to cover, so let's get to it! --- Greg E writes: Hello Jeff, I just found your blog and wanted to know if you could point me in the right direction or possibly toss me a solution. I am looking at a badly formed telelphone number column in a MS SQL Server db. Entries contain '(555) 555-1212' or '555.555.1212, etc. Do you know how I would go about stripping out unwanted characters from the telephone number? Thanks for the brain cycles. Greg -- A simple UDF should do the trick for you.  For example,...

posted @ Wednesday, July 16, 2008 4:32 PM | Feedback (0) | Filed Under [ Miscellaneous ]

Powered by:
Powered By Subtext Powered By ASP.NET