Tuesday 25 August 2009

List tables files and filegroups

Here's a simple query to get a list of database tables and the files and filegroups they are assigned to:
I found this helpful tip on blog.sqlauthority.com and added a few little bits for better readability.

Friday 14 August 2009

Calculating British Summer Time (BST)

I ran into a problem where time sensitive data was being sent to Sales staff. Our servers are permanently set to Greenwich Mean Time (GMT) to avoid problems with scheduled tasks on the days when the clocks change. Sales staff, however were continually baffled by the GMT times when we were in BST time. So I wrote a User Defined Scalar Function to return an bit value indicating whether a date falls within BST or not.
The function returns 1 if the date falls within BST
The function returns 0 if the date falls outside BST

BST is the same as European Summer Time. Clocks move forward and hour on the last Sunday of March and move back again on the last Sunday of October.

I've modified this function to calculate Daylight Saving Time in the United States (USA) - See below. Clocks move forward and hour on the second Sunday of March and move back again on the first Sunday of November.

UPDATE: Based on the comment from Howard (see below): Please use this script:
/*****************************
My old and clumsy script:
*****************************/
Britain and Europe:
United States:
Example of usage: