Tag: SQLNewBlogger

I was working with a customer that was doing some error handling in procs and helped them do some error tracking. As we were working through things, I realized that some of functions working with errors operated differently than I expected.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The Error Functions

There are a number of error functions available to you in modern SQL Server. We have:

• ERROR_LINE()
• ERROR_MESSAGE()
• ERROR_NUMBER()
• ERROR_PROCEDURE()
• ERROR_SEVERITY()
• ERROR_STATE()

All of these functions have the same clause in their docs, which says, “ xxx returns NULL when called outside of the scope of a CATCH block.”

That was something I didn’t realize. I’d assumed I could run this:

SELECT 1/0

SELECT ERROR_SEVERITY(), ERROR_MESSAGE(), ERROR_STATE()

However, if I run this, I get the error, but my results are NULL, NULL, NULL.

If I want the values, I need to do this:

BEGIN TRY
   SELECT 1/0
  

END TRY

BEGIN CATCH
   SELECT ERROR_SEVERITY(), ERROR_MESSAGE(), ERROR_STATE()

END CATCH;

This will return my 16, Divide by zero error encountered., 1

In general, you ought to be using TRY..CATCH blocks for error handling. We do want to ensure that we are doing our best to deal with problems in code and not just expect all errors will be managed by the application. As much as possible, we should try to gracefully fail and give the application or client something useful.

Along with TRY..CATCH, learn to use THROW, and ensure you’re adding some error handling to older code. This is an easy refactoring add to existing code, and it’s simple to enhance future code to make it more maintainable.

SQL New Blogger

This is a quick look at the functions that capture error information, and noting a limitation I didn’t realize. It’s short, simple, and took me about 10 minutes.

This is one of those topics that dev managers, especially front end based ones, appreciate. Doing a post on this topic on your blog might get someone to ask you about error handling, and with a little practice (and a few posts), you’ll be able to talk about this topic confidently.

I saw someone using DATETRUNC recently in some code and realized I hadn’t really looked at this function before. It’s one that was added in SQL Server 2022, though it’s been in other platforms for years.

This post looks at the basics of this function.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

DATETRUNC

One of the challenges for years in SQL Server is dealing with dates. For years we had datetime, and we used this for everything. However, this includes dates and times. The DATE datatype was eventually added, but we have lots of legacy data that includes dates and times mixed together.

Since we don’t always want dates and times, or we want some cutoff, the DATETRUNC function was added to help us. This function takes two parameters, a datepart and a date.

The datepart is any sort of potion of a datetime value. This can be quarters, months, hours, minutes, milliseconds, etc. Of course, all as singular, not plural.

The date is any valid date type: smalldatetime, datetime, date, time, datetime2, datetimeoffset.

We use it like this:

SELECT GETDATE(), DATETRUNC(DAY, GETDATE())

That returns on my system:

----------------------- -----------------------
2023-12-13 18:23:00.337 2023-12-13 00:00:00.000

If you look, this has truncated the date at the day, replacing everything after this with zeros. In this case, the datetime output of getdate() is turned into a date value.

Another example, what if I want to get rid of seconds? I can do that easily like this:

SELECT GETDATE(), DATETRUNC(SECOND, GETDATE())

———————– ———————–
2023-12-13 18:24:19.557 2023-12-13 18:24:19.000

You can see that I have the same date and time for hours, minutes, and seconds, but I’ve gotten rid of the partial seconds.

Using This Function

This is a function, and using it in the WHERE clause (or ON) can impact performance. This often (maybe always) messes up your index usage. However, we often want to display something cleaner, and perhaps in the SELECT clause we want to just order things and show hours.

I might to show shipments during an hour and this code helps:

SELECT TOP 50
        o.OrderID
      , o.Customer
      , o.OrderDate
      , DATETRUNC (HOUR, o.OrderDate) AS OrdersByHour
FROM dbo.[Order] AS o
ORDER BY o.OrderDate desc;

770         0SW2LZ               2023-12-12 23:14:35.220 2023-12-12 23:00:00.000
830         X6SYVULIQQGMZLPN0LL  2023-12-12 23:08:22.450 2023-12-12 23:00:00.000
731         NB3                  2023-12-12 23:03:45.120 2023-12-12 23:00:00.000
883         UDPUS144L1SL1Z1KPD   2023-12-12 22:56:25.100 2023-12-12 22:00:00.000
171         M28F5EYLB            2023-12-12 22:56:07.950 2023-12-12 22:00:00.000
775         P9LET1EBNFN          2023-12-12 22:53:48.580 2023-12-12 22:00:00.000
209         S1I4Q04SUOP          2023-12-12 22:19:49.470 2023-12-12 22:00:00.000
654         5O4GBEWZZVDII        2023-12-12 22:14:53.420 2023-12-12 22:00:00.000
967         NWA9                 2023-12-12 22:06:04.400 2023-12-12 22:00:00.000
458         JYD4TZU0S35XPW3WD7   2023-12-12 22:01:14.350 2023-12-12 22:00:00.000
584         ZDQ2J348SRI6D3HW     2023-12-12 21:59:34.910 2023-12-12 21:00:00.000
718                              2023-12-12 21:54:32.740 2023-12-12 21:00:00.000
359         I4YDWI               2023-12-12 21:54:20.970 2023-12-12 21:00:00.000

 

If I look at these results, it’s cleaner to see the hours, and this certainly is easier than parsing our and combining years, months, days, and hours.

There are likely lots of uses for cleaning up output, or limiting input parameters to certain groups of date values. Definitely a function I can see myself using to simplify and group date data in new ways.

SQL New Blogger

This post took me about 15 minutes to write, including the mockup of some code and generating some data with SQL Data Generator. I did a basic exploration of this function, and wrote about it.

This is something you can easily do, and include your own thoughts on where you’d use this. Search your old code for DATEPART stuff and see if you can replace some complex expressions with DATETRUNC.