Changing Time!

For those wondering if their jobs will run twice (or not at all) because of the daylight time changes in the next few months, here are a couple of links to help

Daylight Time and SQL Server Jobs

and for those using SQL Server 2005 and 2000,

Preparing for Time Changes

TTFN

I'm counting out time, hoping it goes like I planned it

Title courtesy of Genesis ("The Lamb Lies Down On Broadway").
[Tech Stuff]
A somewhat strange title, you might think, but I just had the task of re-writing a couple of T-SQL functions that calculate times. Numbers of days in an interval, to be more precise. Someone had either written or copied a solution that was kind-of working; I needed the real right answers.

Here's the version for calculating weekdays inclusive between two dates. The "inclusive" means that if you're asking "how many days from Tuesday to Thursday" you really mean "how many days work from the instant that Tuesday begins to the very last moment in Thursday - all 72 hours of it. It may be a little difficult to read - use [Ctrl] & [+] to make it bigger in your browser - but I didn't want to get lots of line-wraps.


ALTER function [dbo].[fnCalcWeekdaysInPeriod]
( 
@daStartDate date, 
@daEndDate date 
) 
returns int 
as
/*
select dbo.fnCalcWeekdaysInPeriod('9/29/2012', '9/30/2012')
*/
Begin 
-- ---------------------------------
if @daStartDate > @daEndDate return 0
declare @intDays int, @intWeeks int, @intResDays int
-- ---------------------------------
-- We're only interested in Mon - through -  Fri, 
-- so adjust boundary weekends out of the calculation
-- Push the start date on two for Sat, one for Sun.
-- Pull the end date back two for Sun, one for Sat.
-- ---------------------------------
if (datepart(dw, @daStartDate) = 7) set @daStartDate = dateadd(d, 2, @daStartDate)
if (datepart(dw, @daStartDate) = 1) set @daStartDate = dateadd(d, 1, @daStartDate)
if (datepart(dw, @daEndDate) = 1) set @daEndDate = dateadd(d, -2, @daEndDate)
if (datepart(dw, @daEndDate) = 7)  set @daEndDate = dateadd(d, -1, @daEndDate)
-- ---------------------------------
-- Get the days  --  plus one to make the count inclusive.
-- ---------------------------------
set @intDays = datediff(d, @daStartDate, @daEndDate) + 1
-- ---------------------------------
-- If negative then start date has met end date - which shouldn't happen unless they're very close
-- ---------------------------------
if @intDays < 1 set @intDays = 0
-- ---------------------------------
-- Find out how many weekends there are between the dates
-- ---------------------------------
set @intWeeks = cast((cast(@intDays as float) / 7) as int)
-- ---------------------------------
-- Subtract 2 for every internal weekend
-- ---------------------------------
set @intResDays = @intDays - (@intWeeks * 2)
-- ---------------------------------
return abs(@intResDays)
End

If anyone can find a problem with it then I'd be happy to buy the first few people a beer each! The next post will be for the same thing but for work days - i.e. the days people work after taking company holidays off. It depends on this, though, so this needs to be correct!

--------------------------------

On a more personal note, my cousin Edward now has a berth on the Queen Mary II. Congratulations and best wishes to him!

Go here for information on the ship.

Go here for booking information (shameless plug!).

--------------------------

[Food]
My darling wife and I collaborated on dinner last night. This isn't a cause for a blog in and of itself, but it turned out more successfully than we'd expected. It was a very simple meal - chicken breast, couscous, and some sauce, with a few veggies - but it turned out to be very nice indeed, and pretty low-effort.

Ingredients

Two (or more!) chicken breasts	Chicken broth
Dried Basil and Bay Leaf, crushed up	Salt and Pepper
Spring Onion, chopped small	Celery, chopped fairly small
CousCous	Cream Cheese
Roast Garlic Jelly	Butter

Instructions
1. Clean the chicken breasts well, trimming off any fat you don't want to serve.
2. Lay each breast on a piece of aluminium foil big enough to fold up loosely around the whole breast.
3. Turn up the foil around the edges  and pour in some broth, enough  to reach about a centimetre
    up the breast.
4. Strew the breast with the herbs.
5. Fold up the foil over the breasts to close them in, put them all on a baking tray, and bake
    for 35-40 minutes at between 380F and 400F.
6. For the sauce, take about 10 oz of cheese, about the same of the roast garlic jelly, and some
    chicken broth to help thin it out. Put it all into a saucepan and whisk gently to mix while heating through.
7. For the couscous, heat some butter (maybe a quarter of a stick) in a pot, then pour in about 3/4 cup of
    courcous per person. Stir while still heating, so that the couscous is a little fried in butter. When you can
    hear the mixture start to sizzle a little, add a cup of chicken broth and stir well. Stir in some more celery
    and spring onion, and any aromatics that catch your eye - such as a little Rosemary or Thyme for
    flavouring to your taste.

Eat well!

TTFN

--------------------------