Category Archives: SQL

Date Calculations and Daylight Saving Time

Many years ago I worked for an energy business. One nuisance we had to deal with was the “clock shift” when BST (British Summer Time) began or ended. The official prescription for the clock change in the United Kingdom is found at
https://www.gov.uk/when-do-the-clocks-change
“In the UK the clocks go forward 1 hour at 1am on the last Sunday in March, and back 1 hour at 2am on the last Sunday in October.” So the megawatt hours supplied in a contract starting outside BST and ending inside BST would be one hour less than expected, while a contract starting inside BST and ending outside BST would total one megawatt hour more than expected.

Since then I haven’t thought of it much but I was listening to a podcast where a DBA mentioned problems using Sql Server’s GETDATE() or SYSDATETIME() functions Continue reading Date Calculations and Daylight Saving Time

A Column-level Query No-No in SELECT statements

You may sometimes want to do a lookup and return it as a column in your query results – but you don’t want to reference the source table in the FROM clause. In this case you may decide to use a column-level query. I’m not arguing the rights or wrongs of column-level queries, either on performance or theoretical grounds. The problem is that if this approach is applied naively it’s a bug. Continue reading A Column-level Query No-No in SELECT statements

Debugging sql joins and filters in Oracle

This is a basic tip that will save time when a query isn’t returning the expected results (typically, it’s bringing back an empty set) and you need to find out which join or WHERE clause filter is causing the problem. It’s not impossible to do, of course, but it’s nice to have a method that gets a result quickly without too many edits. Take the following query, which should run on your Oracle instance: Continue reading Debugging sql joins and filters in Oracle

Sql Tricks for Tricky Logic (3)

This is the last posting in my brief discussion of SQL tricks. I’m dealing here with an issue which causes quite a bit of confusion to newer developers. What’s the point in having LEFT and RIGHT joins defined in the SQL standard, how do you decide which to use, and is there a way to keep things simple? Continue reading Sql Tricks for Tricky Logic (3)

SQL Tricks for Tricky Logic (2)

I’ve been working with Linq in .NET lately and there have been a few posts I would have liked to add but since I said I was going to blog SQL tricks I’m putting other topics to one side for the moment.

So… another situation where it would be useful to have an SQL trick is the case of the filtered outer join. Continue reading SQL Tricks for Tricky Logic (2)

SQL Tricks for Tricky Logic

One reason occasionally offered for using dynamic SQL widely, or even for keeping business logic out of database stored procedures, is the difficulty of coding optional filters in SQL. Sometimes we may want a row from table X where column A = this, column B = this and column C = this. Other times we may want all the rows where column A = this but we’ll accept any value in columns B and C. Continue reading SQL Tricks for Tricky Logic