Tag: T-SQL

FORMATing Dates
I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

FORMAT is a function that was introduced in SQL Server 2012. It is designed to format dates and numbers as date/times. It was added to try and reduce the complexity and cumbersome nature of CONVERT and CAST.

What I didn’t know, which I did like, is that the FORMAT command can be driven by language settings. For example, if I take a specific date, like today, I can reformat the date based on a language code.
```
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result'
      ,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result'
      ,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result'
      ,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result'
```
The results of this code are different, based on the cultural settings. Of course, the Chinese settings is really the only good way to show dates without confusion.

This is interesting in that if you can get the regional settings for the client, you can easily return data in a format that makes sense to the user. Of course, we typically don’t want to do too much formatting on the server.

The default language is that of the current session. For dumb uni-lingual people like me, this isn’t an issue, but it might matter for people that speak multiple languages and might move from system to system.

It is interesting in that you can easily format the data from a date with FORMAT. For example, I change orders to match the Chinese format:
```
DECLARE @d DATETIME = GETDATE();

SELECT FORMAT( @d, 'YYYY/MM/DD');
```
That gets me “2015/07/20”.

I can easily change to other formats, adding in times, or even partial times that might not make sense. For example, the result below named “Hours” has only the date. The “Date and hours” has the date and the hour only.
```
DECLARE @d DATETIME = GETDATE();

SELECT  'Default' ,
        FORMAT(@d, 'd')
UNION
SELECT  'Full Date' ,
        FORMAT(@d, 'YYYY/MM/DD hh:mm:ss.ffff t zzz')
UNION
SELECT  'Date and hours' ,
        FORMAT(@d, 'YYYY/MM/DD hh')
UNION
SELECT  'Date and minutes' ,
        FORMAT(@d, 'YYYY/MM/DD mm')
UNION
SELECT  'Hours' ,
        FORMAT(@d, 'hh');



--      ,FORMAT ( @d, 'yymmdd', 'en-gb' ) AS 'CleanUS'
```
The results are:

Date and hours    2015/11/20 04

Date and minutes 2015/11/20 11

Default           5/20/2015

Full Date         2015/11/20 04:11:28.9600 P -06:00

Hours             04

I think that there are lots of reporting queries where the formatting of dates would be handy and easier with FORMAT than CONVERT. It certainly is more intuitive to read than seeing something like “,110” in code.

I still have the habit of using CAST and CONVERT when I’m changing types, and I’ll continue to do that. Especially as FORMAT is really limited to date types. However when trying to get dates to render in proper formats, it’s a good choice.

SQLNewBlogger

This post came about while I was checking on another issue. I happened to run into the FORMAT command and hadn’t used it much, so I spent a few minutes messing around. This post came out of around 5 minutes of experimentation and 15-20 minutes of writing.

References

FORMAT – https://msdn.microsoft.com/en-us/library/hh213505.aspx

Formatting Types in the .NET Framework – https://msdn.microsoft.com/library/26etazsy.aspx
June 3, 2015

Visualizing the Tally Table

I was reading Dwain Camps’ article on Time Slots and thought it was a very interesting solution to a problem I’ve had a few times. Getting time slots inside of a period that I want to query. If you have a similar need, or want to learn more, I’d urge you to read the article.

It’s always easier to join to a set of data that matches what you need than to try and filter out other rows. SQL excels at joins, so whenever possible you want to join to data. As such, when I was looking at Dwain’s code, I thought the way he listed the tally table was very interesting. I’ve seen plenty of these generated, but I hadn’t run across someone spelling it out in comments. In case you are wondering, Dwain had code like this:

WITH Tally (n) AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1                   -- zero-based
    -- Returns exactly 86400 rows (number of seconds in a day)
    FROM       (VALUES(0),(0),(0),(0),(0),(0))                         a(n) -- 6 rows 
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) -- x12 rows
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) -- x12 rows
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))         d(n) -- x10 rows
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))         e(n) -- x10 rows
),                                                                          -- = 86,400 rows

That’s a great visualization, and one I plan on using in the future. It makes it easy to see what’s being generated and at what scale.

For example, I can easily do this:

SELECT
        ROW_NUMBER() OVER ( ORDER BY ( SELECT
                                            NULL
                                     ) )
    FROM
        ( VALUES ( 0), ( 0) ) a ( n )                   -- 2 rows

which returns a single column table with the values 1 and 2 in it. Two rows.

Let’s say I want to now build a list of 12 rows. I could do this a few ways. One is to multiple 2 x 6 and get 12.

SELECT
   ROW_NUMBER() OVER (ORDER BY ( SELECT NULL))
 FROM
   ( VALUES (0), (0) ) a(n)         -- 2 rows
   CROSS JOIN 
   ( VALUES (0), (0), (0), (0), (0), (0) ) b(n);  -- x 6
;

Or I could give myself more flexibility to add and remove data with comments by doing factorials. How about 2 x 3 x 2 = 12?

SELECT
   ROW_NUMBER() OVER (ORDER BY ( SELECT NULL))
 FROM
   ( VALUES (0), (0) ) a(n)         -- 2 rows
   CROSS JOIN 
   ( VALUES (0), (0), (0) ) b(n)  -- x 3
   CROSS JOIN 
   ( VALUES (0), (0) ) c(n)  -- x 2
;

That gives me the same result: 12 rows. Of course, I can easily expand this quickly to thousands of rows.

The technique isn’t anything new, but the visualization is interesting, and to me, this is much easier technique to see and understand when you run into it in code. Right away I know I’m generating xx rows and I can easily see how to grow or shrink the number of I have the need.

May 27, 2015

Changing NULL to NOT NULL – Dealing with Data

I wrote recently on how to alter a column in SQL Server from NULL to NOT NULL in a simple way. However I didn’t cover some of the cases where you have data in the column, or other restrictions that you might need to deal with. In this post, I want to look at a few options if you have data in the table.

Let’s take my SimpleTable and add data:

INSERT Simpletable VALUES (1, ‘A‘, 1) , (2, ‘B‘, 0) , (3, ‘C‘, NULL) , (4, ‘D‘, NULL) ;

Now I have 4 rows. If I run this:

ALTER TABLE dbo.SimpleTable ALTER COLUMN Status TINYINT NOT NULL;

I get this error:

Msg 515, Level 16, State 2, Line 2

Cannot insert the value NULL into column ‘Status’, table ‘Sandbox.dbo.SimpleTable’; column does not allow nulls. UPDATE fails.

The statement has been terminated.

Essentially, SQL Server can’t mark this column as NOT NULL because there are NULLs in there. What can I do here?

UPDATE the Table

The only thing I can do is to change the data. I can run a query that removes all the NULLs in the column by setting them to some value. That’s what I’ll do here. I can choose a value here and run an UPDATE statement. Something like this:

UPDATE dbo.SimpleTable SET Status = 0 WHERE status IS NULL;

Once I do that, I can easily run my ALTER and it succeeds.

May 25, 2015
Refactoring Mistakes Are Why We Write Tests
I wrote a short piece the other day trying to show how one can use tSQLt to test code. It’s a simple test built against a user defined function. It works well and when the test is run, it passes.

Here was my code:
```
ALTER function [dbo].[calculateEstimateOfReadingTime] ( @value varchar(max) )
returns int
as
    begin
      declare
        @ret as int = 1
      , @i as int = 1;
      while @i <= len(@value)
        begin
          if substring(@value, @i, 1) = ' '
            begin
              set @ret = @ret + 1;
            end
          set @i = @i + 1;
        end
      return @ret / 250;
;    end
```
Someone in the comments pointed out that we can more efficiently refactor this code to :
```
ALTER FUNCTION [dbo].[calculateEstimateOfReadingTime] 
( 
    @value varchar(max) 
)
RETURNS int
AS
BEGIN
RETURN (
    SELECT LEN(@value) - LEN(REPLACE(RTRIM(@value), ' ', '')) + 1
)
END
```
However when I run the test, I get these results:

That’s not good, but that’s why we test.

I could easily see someone refactoring the code, finding a more elegant method of rewriting this code and after running some quick tests, they check this in to source control (hopefully) and maybe deploy it to production. Hopefully QA catches this, but wouldn’t we want to notice this in development?

The refactored code misses a divide by 250.

Write tests, use them to catch mistakes. These simple ones slip through at times and are what make deployments really, really stressful.
May 20, 2015

Tag: T-SQL

FORMATing Dates

SQLNewBlogger

References

Visualizing the Tally Table

Changing NULL to NOT NULL – Dealing with Data

UPDATE the Table

Refactoring Mistakes Are Why We Write Tests