Tag: T-SQL

I ran across a post recently where someone had dates stored as characters (never good), but also in this format: CYYMMDD. I’d never seen that, and thought it was strange. The person was asking for a way to convert this to YYYY-MM-DD, which I think it fairly easy.

Let’s set up some data:

CREATE TABLE RandomDates
(
    myid INT
    , mydate VARCHAR(7)
);
GO
INSERT dbo.RandomDates
        ( myid
        , mydate
        )
    VALUES
        ( 1, '0600102' )
       , ( 2, '1121004' )
       , ( 3, '0920415' )
       , ( 4, '1040611' )
        ;
GO

The format for the data is 7 characters denoted as century, year, month, day. The century is encoded, with the single value representing:

• 0 = 1900
• 1 = 2000

That’s pretty straightforward and it means that we have two dates in the 1900s and 2 in the 2000s. The dates are:

• January 2, 1960
• April 15, 1992
• June 11, 2004
• October 4, 2012

Querying these dates and converting them is straightforward, but there are a couple ways to do this. If we are just looking to convert these to characters, I saw this solution from ZZartin.

 SELECT CONVERT(DATETIME, CASE 
                            WHEN LEFT(mydate, 1) = '1' 
                                THEN '20' 
                                ELSE '19' 
                            END 
                            + RIGHT(rd.mydate, 6)
                , 112)
  FROM dbo.RandomDates AS rd;

That’s fairly straightforward, and overall I like it. It uses simple functions and puts things together.

I had another idea, mostly because I initially favored keeping the items separate as parts of the date in case I needed them. I thought about DATEFROMPARTS, which is a SQL Server 2012+ function. My thought was to calculate each of the parts and send them into the function like this:

SELECT DATEFROMPARTS(
                    CASE WHEN LEFT(mydate, 1) = '0'
                        THEN 1900
                        ELSE 2000
                        END + SUBSTRING( rd.mydate, 2, 2)
                    , SUBSTRING( rd.mydate, 4, 2)
                    , SUBSTRING( rd.mydate, 6, 2)
                    )
 FROM dbo.RandomDates AS rd
 ;

My idea has more function calls, and I’d think it would take longer to build, but I’m not sure. Let’s test.

I’ll use Data Generator to insert a few million rows into this table. Then let’s run both pieces of code.

The first code, from ZZartin, required about 19,000 logical reads and this execution plan when I ran it a few times. The CPU execution was in the 40k ms, and about 9 sec of real time.

The second code, mine, had about 30,000 logical reads, with only 8k CPU ms, but about 40sec of real time. The execution plan:

That’s interesting, and it matches what I’d expect. The first code is much simpler, intuitively, and it’s easy to read. With the format of the date essentially in order, it doesn’t make sense to try and "assemble" the date from parts. It’s easier to convert the first character to two (with the CASE) and then just cast this as a date.

I wrote briefly about templates in Management Studio (SSMS), and showed the default templates that come with SQL Server. I now want to customize some of the templates in a way that makes sense for me.

If I grab a script I use often, like this one, I can make it generic.

SELECT
        username
    ,   topic
    ,   COUNT(replies)
    FROM
        users u
        INNER JOIN posts p
        ON u.userid = p.userid
    WHERE
        u.email = 'bob@bob.com'
    GROUP BY
        username
    ,   topic;

I run this often to check things, but I rarely need Bob’s information. Instead, I’ll often get different users, and sometimes I need dates. I can add these changes:

SELECT
        username
    ,   topic
    ,   COUNT(replies)
    FROM
        users u
        INNER JOIN posts p
        ON u.userid = p.userid
    WHERE
        u.email = '<email, varchar, bob@bob.com>'
    AND startdate > <startdate, datetime, dateadd(m, -1, getdate())> 
    AND enddate <lessthan, char <> <enddate, datetime, getdate()>
    GROUP BY
        username
    ,   topic;

I’ve changed some of my variable items to parameters. I do this by taking an item that I want to make variable, like “bob@bob.com” and changing it to “<email, varchar, bob@bob.com>”.

The format for a template is:

• name
• type
• default

all of which are placed inside angled brackets and separated by commas. Now when I click CTRL+Shift+M, I get this:

I can click OK for the defaults to be placed in the script, or I can enter new ones. Either way, I save time and effort with saved queries, but saved as templates, not queries I need to edit constantly.

UPDATE: Someone pointed out that the less than, the <, was . I got this from Stack Overflow, which had a good solution. I made the < a parameter as well.

I wrote about the Template Explorer, which comes with the SQL Server tools and is visible in Management Studio (SSMS). It’s handy, but there are limited code items in there. What if I want more?

That’s easy. Suppose I decide that I often need to create procedures with the EXECUTE AS clause. I usually do this:

CREATE PROCEDURE MyProc
   @id INT
WITH EXECUTE AS OWNER
AS
BEGIN

-- do work
BEGIN TRY

  COMMIT
END TRY
BEGIN CATCH
  ROLLBACK
  EXEC uspErrorHandler;
END CATCH

END

It’s a basic template of stuff I do. Let’s stick this in our Template Explorer.

The first thing I do is go to the Stored Procedure folder. I can right click it and I’ll see this:

I choose template and a new one is created. I enter a name and I have a template. The first 6 templates here are defaults. The last one, highlighted below, is the one I created.

Now I right click it again and select Edit. At this point, it will open in a query window. This is just a file in my file system (under ), and like any other query, I can edit it. I paste in my script from above, and change a few items to parameters.

Now I can save this, and the next time I need this, just drag it into the main window and customize it.