T-SQL Syntax | Bob Pusateri - SQL Server and Kindred Subjects

Finding Month Ends in T-SQL

Feb 282018

Months are funny. Unlike other parts of a date, they vary in length:

The last second of a minute is always 59.
The last minute of a hour is always 59.
The last hour of a day is always 23.

But the last day of a month? Well that depends on what month it is. And the year matters too because a leap year means February gets an extra day.

This used to be somewhat tricky to calculate, but then for SQL Server 2012 Microsoft added the EOMONTH() function (End Of MONTH) to T-SQL to help with this. EOMONTH() takes a DATE or DATETIME, and it will return the DATE (with no time component) of the last day of that month. Here’s a basic example:

SELECT EOMONTH('20180219');

1	SELECT EOMONTH('20180219');

It also accounts for leap years. Let’s try a date in February 2016:

SELECT EOMONTH('20160224');

1	SELECT EOMONTH('20160224');

What about other months?

In the case that you need the last day of a different month than the current one, you can make use of EOMONTH()‘s second parameter. This allows you to add (or subtract) months from the date you specified:

-- Second parameter adds that number of months to the date
SELECT EOMONTH('20180220') AS CurrentMonthEnd,
       EOMONTH('20180220', -1) AS PreviousMonthEnd,
       EOMONTH('20180220', 1) AS NextMonthEnd;

-- Second parameter adds that number of months to the date

SELECT EOMONTH('20180220') AS CurrentMonthEnd,

EOMONTH('20180220', -1) AS PreviousMonthEnd,

EOMONTH('20180220', 1) AS NextMonthEnd;

What if I need the first day of the month?

There’s no similar function to calculate the first day of the month, but think about it: the first day of the month is very predictable. It’s always day 1. You can also use EOMONTH() to find the first day of a month by adding one day to the last day of the previous month:

SELECT DATEADD(DAY, 1, EOMONTH('20180220', -1)) AS FirstDayOfMonth;

1	SELECT DATEADD(DAY, 1, EOMONTH('20180220', -1)) AS FirstDayOfMonth;

What if I don’t have SQL Server 2012?

If you’re still running SQL Server 2008R2 or older, my first piece of advice is to please look into upgrading to something more current soon. As of this writing, SQL Server 2008R2 Service Pack 3 is only supported until 9 July 2019. If you’re not running Service Pack 3, you are already unsupported.

Without the EOMONTH() function, calculating the end of the month gets a bit more complex. There are several ways to do it in T-SQL, but this is my preferred method:

SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) - 1 AS DATE)

1	SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) - 1 AS DATE)

This works by utilizing some creative math with dates. I’ve highlighted the different sections of the statement to explain what’s happening:

What if I don’t have a computer?

I’ve got a solution for this one too! Ever heard of the knuckle trick?

Make two fists and put them next to each other so your knuckles are lined up. Each high and low point along your knuckles represents a month, and the high points represent months with 31 days. Low points are months with less than 31 days (28/29 for February, 30 for the rest).

Source: Wikipedia

SQL Prompt 7.2 – Now With Execution Warnings!

T-SQL Syntax, Tools 4 Responses »

May 102016

I’ve loved using Redgate’s tools ever since I discovered what they were, and now that I’m a Friend of Redgate it’s even more fun because I get to give feedback to their developers and hear all about what’s coming out in new releases! Recently, Redgate announced SQL Prompt 7.2, with a bunch of new features and improvements. My personal favorite of all these is execution warnings.

Databases (and computers in general) have this pesky habit of always doing exactly what we tell them to do, instead of doing what we really meant to tell them to do. Have you ever been burned by running a query without the WHERE clause? Perhaps you ended up updating or deleting ALL the rows in a table instead of just a few? A common way to reduce the risk of this is to run those commands inside a transaction, and if you see an abnormally high number of rows affected, it’s simple to rollback. This works great, until you’re in a hurry and forget to run BEGIN TRAN, greatly upping the chances of disaster. Now in SQL Prompt 7.2 you have an added layer of protection – the tool is watching your queries and can warn you! Check it out in action:

If I try to update my table of important data and don’t specify a WHERE clause, I’ll see the following:

The same happens for deletes:

And I think it’s great that I have the option of checking the box and not showing that warning again, but I definitely won’t be doing that.

A lot of times it’s the little things that really make a difference, and I think these warnings are a simple and unobtrusive way to make sure you really meant to run what you typed.

Dropping Multiple Databases At Once

SQL Server, T-SQL Syntax 4 Responses »

Dec 032015

I’m already at peace with the fact that I’ll never know all of SQL Server’s secrets, but that doesn’t stop me from being surprised every time I figure out something new. In this case, it’s another “secret” hiding in plain sight (Books Online).

It turns out that the DROP DATABASE statement doesn’t just have to drop one database. BOL shows that multiple databases can be specified when separated with commas. Let’s see it in action.

First, create 4 databases:

CREATE DATABASE DB1;
CREATE DATABASE DB2;
CREATE DATABASE DB3;
CREATE DATABASE DB5;

CREATE DATABASE DB1;

CREATE DATABASE DB2;

CREATE DATABASE DB3;

CREATE DATABASE DB5;

Now drop them:

DROP DATABASE DB1, DB2, DB3, DB5;

1	DROP DATABASE DB1, DB2, DB3, DB5;

Yep, all gone.

But what if there’s an error? Re-run the CREATE statements above, but now let’s drop 5 databases instead of 4. DB4 doesn’t exist (much like Terminal 4 at Chicago’s O’Hare Airport).

DROP DATABASE DB1, DB2, DB3, DB4, DB5;

1	DROP DATABASE DB1, DB2, DB3, DB4, DB5;

The above statement will throw an error that it is unable to drop DB4 because that database doesn’t exist, but the other 4 databases are dropped without incident. The same will happen if a user is connected to one of the databases: that one will remain, but all others will be dropped.

So there you have it: you can drop multiple databases with a single statement in SQL Server. (According to Books Online this does not work in Windows Azure SQL Database.) It’s amazing the things you can learn from reading documentation!

T-SQL Tuesday #025: Trick or …?

SQL Server, SQLServerPedia Syndication, T-SQL Syntax, TSQL Tuesday, XML 1 Response »

Dec 132011

This month’s T-SQL Tuesday is hosted by Allen White (blog | @SQLRunr) and is an invitation to show off a nifty T-SQL trick you use to make your job easier. Here’s one of my favorites as of late:

Some of the source systems we copy from shard their data across multiple similar tables. For example, instead of having 1 table named dbo.Sales, they might have 30 of them named dbo.Sales_001 to dbo.Sales_030. If there were a rhyme or reason to this sharding it might be seen as an advantage, but unfortunately neither myself nor others on the team have ever found a method to this madness, and the vendor will not divulge the way they do this. As if that’s not bad enough, additional tables can pop up out of nowhere, and not all these tables are guaranteed to contain the same columns. Table dbo.Sales_031 may pop up tomorrow and have only a subset of the columns from the previous 30 tables, or may contain new columns not present in any of the others.

To keep this all straight, I have a process that compares the columns in all applicable tables and generates a view that combines their contents using UNION ALL. I’ve actually blogged about an aspect of this before, but today I have a function containing a nice little trick that’s proven very helpful for me. Since the tables I’m generating a view of aren’t guaranteed to have the same columns in them, a simple “SELECT *” from each of them won’t work because unioning tables together requires identical datatypes in the same order. Instead I have to generate a select statement that explicitly lists all columns for every table. T-SQL can easily accomplish this with cursors and loops, but then I found FOR XML PATH, which made life a lot simpler.

FOR XML PATH has many features which are outside the scope of this post, but the one behavior we’re going to exploit today is that you can pass a row tag name that’s blank. When you do this, instead of sandwiching each row between tags, it simply concatenates all the row values together into one string. If you add commas, you’ve got a great way to list out all the columns of a table which you can then form into a SELECT statement. Without any further ado, here’s the demo code:

-- create our demo table with a few columns
CREATE TABLE dbo.MyTable (
   ID INT NOT NULL,
   Value VARCHAR(20),
   Notes NVARCHAR(MAX)
);

-- select the table's columns from system tables
SELECT c.name
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'MyTable'
ORDER BY c.column_id;

-- now let's format it as XML
SELECT c.name
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'MyTable'
ORDER BY c.column_id
FOR XML PATH;

-- now pass a blank string argument to PATH
-- note how the &lt;row&gt; tags are now removed but column tags remain
SELECT c.name
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'MyTable'
ORDER BY c.column_id
FOR XML PATH('');

-- casting it to NVARCHAR removes the tags for each column
DECLARE @s NVARCHAR(MAX);

-- now let's add a blank string argument to PATH
SET @s = (
      SELECT ',' + QUOTENAME(c.name) -- comma to separate names
      FROM sys.columns c
      INNER JOIN sys.tables t ON c.object_id = t.object_id
      WHERE t.name = 'MyTable'
      ORDER BY c.column_id
      FOR XML PATH('') -- XML that really isn't
   );

-- remove the leading comma
SELECT SUBSTRING(@s, 2, LEN(@s));

-- create our demo table with a few columns

CREATE TABLE dbo.MyTable (

ID INT NOT NULL,

Value VARCHAR(20),

Notes NVARCHAR(MAX)

);

-- select the table's columns from system tables

SELECT c.name

FROM sys.columns c

INNER JOIN sys.tables t ON c.object_id = t.object_id

WHERE t.name = 'MyTable'

ORDER BY c.column_id;

-- now let's format it as XML

SELECT c.name

FROM sys.columns c

INNER JOIN sys.tables t ON c.object_id = t.object_id

WHERE t.name = 'MyTable'

ORDER BY c.column_id

FOR XML PATH;

-- now pass a blank string argument to PATH

-- note how the <row> tags are now removed but column tags remain

SELECT c.name

FROM sys.columns c

INNER JOIN sys.tables t ON c.object_id = t.object_id

WHERE t.name = 'MyTable'

ORDER BY c.column_id

FOR XML PATH('');

-- casting it to NVARCHAR removes the tags for each column

DECLARE @s NVARCHAR(MAX);

-- now let's add a blank string argument to PATH

SET @s = (

SELECT ',' + QUOTENAME(c.name) -- comma to separate names

FROM sys.columns c

INNER JOIN sys.tables t ON c.object_id = t.object_id

WHERE t.name = 'MyTable'

ORDER BY c.column_id

FOR XML PATH('') -- XML that really isn't

);

-- remove the leading comma

SELECT SUBSTRING(@s, 2, LEN(@s));

Wrap the final output with a SELECT and a table name and you’re good to go. There’s tons more uses for this than creating lists of columns – basically anytime you want to turn column values into a delimited list you can make it happen using this method. Happy coding!

Creating Views in Another Database

SQL Server, SQLServerPedia Syndication, T-SQL Syntax 24 Responses »

Oct 182011

Have you ever had one of those times where you spent hours trying different ways to make something work the way you envisioned, only to find that the solution was staring you in the face all along? I have those more often than I’d like to admit – and while the title of this post seems like a DBA 101 topic, the answer isn’t really all that simple. To hopefully help others (and perhaps my future self) I’ll document a recent one here. I’m sure there will be plenty more in the future as well!

The story goes that I was working on a stored procedure to generate views in other databases via dynamic sql, and the hangup was that you can’t create a view in another database – at least not easily. I realize that views can reference tables in other databases with no problem at all, but due to security restrictions and other issues outside the scope of this post, these views needed to exist in specific databases.

Creating a table in another database is a piece of cake because you can specify the database name in the CREATE TABLE statement:

CREATE TABLE databaseName.schemaName.tableName (...

1	CREATE TABLE databaseName.schemaName.tableName (...

Creating a view, however, is not so easy. You can’t specify a database name in the CREATE VIEW statement – doing so will get you a hot date with error 166. The only remark in Books Online is that “A view can be created only in the current database.”

Since these views were being generated with dynamic sql, my next thought was to issue a USE [OtherDatabase] statement before the CREATE VIEW and just pass all of that into sp_executesql, the idea being that USE will change the context of execution to the desired destination database and all will be well. Unfortunately things didn’t go according to plan:

This yields 3 errors – two for “Incorrect syntax near ‘GO'”, and another that “CREATE VIEW must be the first statement in a query batch.” If GO signals the end of a batch, then removing the GOs makes it all one batch, right? Not quite. Losing the GOs will still give you the error 111, because CREATE VIEW must come first.

So how can we do this? After much experimentation I figured out that you can specify the database sp_executesql runs on by prepending a database and schema name to it just like other objects – something so simple that I thought right past it. It’s kind of crazy to think that in all my years working with SQL Server I’ve never had a need to do it with dynamic sql until now, but that’s the case.