User Defined Functions UDF Tips

With the new rules from the National Institute of Standards and Technology on the Daylight Savings Time calculations, a new set of logic is need to determine if daylight savings time is in effect. Since the new rules have recently been applied to our servers, is there a way in SQL Server to calculate when Daylight Savings Time starts and ends for a given year? Are any built-in functions available or do I need to build a custom solution?

Occasionally I find myself dusting off my old developer coffee mug and sitting down in front of my laptop to do a bit of creative coding when one of our current developers runs up against something they can't get their head around. Recently this was the case with a stored procedure they were attempting to create that needed to return results for a date range based around the day of the week, not the specific date of the month. In this tip I will show you a user defined function that selects dates based on the weekday that is passed.

I have an internally-developed metadata repository for the SQL Server instances I administer. One of the metrics I track is based upon Job History success and failure. This information comes directly from the msdb..sysjobhistory table that resides upon each SQL Server instance. The issue is that there are idiosyncrasies with this table when it comes to storing dates and times of job executions. I need to be able to use this information in reports and queries, but these columns are stored in a non-standard date format, which considering the fact that we're discussing system tables is baffling to me.

I need to write a function to determine if particular year is a leap year (i.e. February contains 29 days rather than 28 days). I know that there are various rules for calculating leap years. Is there an easy way to figure this out? Can you provide an example or two to validate various years?

Working with strings in SQL Server is not as simple as you would hope for a lot of things that you need to do with text data. It is sometimes simpler to do a lot of these things outside of SQL Server, but if you have time and patience to write T-SQL code you could just about do anything you need to do. One of my programmers asked if there was a way to determine if all characters in a string were capitalized. I was not able to find an existing function so I figured I would create my own. Take a look at this function to see if all characters are capitalized in a string.

Unlike other relational database management systems that shall remain nameless, SQL Server's underlying coding language, T/SQL, does not have a built-in function for padding string values. I recently took it upon myself to create my own and as you'll see I got a little carried away.

In a recent tip I outlined a process for converting a date, stored as an integer into a datetime data type. Date and time information for run history of SQL Server Agent jobs is stored within the msdb..sysjobshistory table as an integer data type, not as a datetime as one would expect. Most likely for at least two reasons...

While working on a project recently I was asked to develop a mechanism that would provide the dates for state and federal holidays in a given year. Since this project deals with all states and territories of the United States, the list had to be comprehensive and the client asked that this list be in the form of a SQL query calculated on-the-fly, rather than a static list. To account for all holidays, I grouped each one into one of three categories. First are static holidays. These holidays have the same date every year, Like New Year's Day (01/01) and Independence Day (07/04). Second are dynamic holidays. These holidays occur on a specific day of week in a specific week and month.

I have an internally-developed metadata repository for the SQL Server instances I administer. One of the metrics I track is based upon Job History success and failure. This information comes directly from the msdb..sysjobhistory table that resides upon each SQL Server instance. The issue is that there are idiosyncrasies with this table when it comes to storing dates and times of job executions. I need to be able to use this information in reports and queries, but these columns are stored in a non-standard date format, which considering the fact that we're discussing system tables is baffling to me.

While working on a project recently I was asked to develop a mechanism that would provide the dates for state and federal holidays in a given year. Since this project deals with all states and territories of the United States, the list had to be comprehensive and the client asked that this list be in the form of a SQL query calculated on-the-fly, rather than a static list. To account for all holidays, I grouped each one into one of three categories. First are static holidays. These holidays have the same date every year, Like New Year's Day (01/01) and Independence Day (07/04). Second are dynamic holidays. These holidays occur on a specific day of week in a specific week and month.

With the new rules from the National Institute of Standards and Technology on the Daylight Savings Time calculations, a new set of logic is need to determine if daylight savings time is in effect. Since the new rules have recently been applied to our servers, is there a way in SQL Server to calculate when Daylight Savings Time starts and ends for a given year? Are any built-in functions available or do I need to build a custom solution?

I need to write a function to determine if particular year is a leap year (i.e. February contains 29 days rather than 28 days). I know that there are various rules for calculating leap years. Is there an easy way to figure this out? Can you provide an example or two to validate various years?

Unlike other relational database management systems that shall remain nameless, SQL Server's underlying coding language, T/SQL, does not have a built-in function for padding string values. I recently took it upon myself to create my own and as you'll see I got a little carried away.

Occasionally I find myself dusting off my old developer coffee mug and sitting down in front of my laptop to do a bit of creative coding when one of our current developers runs up against something they can't get their head around. Recently this was the case with a stored procedure they were attempting to create that needed to return results for a date range based around the day of the week, not the specific date of the month. In this tip I will show you a user defined function that selects dates based on the weekday that is passed.

In a recent tip I outlined a process for converting a date, stored as an integer into a datetime data type. Date and time information for run history of SQL Server Agent jobs is stored within the msdb..sysjobshistory table as an integer data type, not as a datetime as one would expect. Most likely for at least two reasons...

Working with strings in SQL Server is not as simple as you would hope for a lot of things that you need to do with text data. It is sometimes simpler to do a lot of these things outside of SQL Server, but if you have time and patience to write T-SQL code you could just about do anything you need to do. One of my programmers asked if there was a way to determine if all characters in a string were capitalized. I was not able to find an existing function so I figured I would create my own. Take a look at this function to see if all characters are capitalized in a string.

Occasionally I find myself dusting off my old developer coffee mug and sitting down in front of my laptop to do a bit of creative coding when one of our current developers runs up against something they can't get their head around. Recently this was the case with a stored procedure they were attempting to create that needed to return results for a date range based around the day of the week, not the specific date of the month. In this tip I will show you a user defined function that selects dates based on the weekday that is passed.

In a recent tip I outlined a process for converting a date, stored as an integer into a datetime data type. Date and time information for run history of SQL Server Agent jobs is stored within the msdb..sysjobshistory table as an integer data type, not as a datetime as one would expect. Most likely for at least two reasons...

Unlike other relational database management systems that shall remain nameless, SQL Server's underlying coding language, T/SQL, does not have a built-in function for padding string values. I recently took it upon myself to create my own and as you'll see I got a little carried away.

I have an internally-developed metadata repository for the SQL Server instances I administer. One of the metrics I track is based upon Job History success and failure. This information comes directly from the msdb..sysjobhistory table that resides upon each SQL Server instance. The issue is that there are idiosyncrasies with this table when it comes to storing dates and times of job executions. I need to be able to use this information in reports and queries, but these columns are stored in a non-standard date format, which considering the fact that we're discussing system tables is baffling to me.

While working on a project recently I was asked to develop a mechanism that would provide the dates for state and federal holidays in a given year. Since this project deals with all states and territories of the United States, the list had to be comprehensive and the client asked that this list be in the form of a SQL query calculated on-the-fly, rather than a static list. To account for all holidays, I grouped each one into one of three categories. First are static holidays. These holidays have the same date every year, Like New Year's Day (01/01) and Independence Day (07/04). Second are dynamic holidays. These holidays occur on a specific day of week in a specific week and month.

I need to write a function to determine if particular year is a leap year (i.e. February contains 29 days rather than 28 days). I know that there are various rules for calculating leap years. Is there an easy way to figure this out? Can you provide an example or two to validate various years?

With the new rules from the National Institute of Standards and Technology on the Daylight Savings Time calculations, a new set of logic is need to determine if daylight savings time is in effect. Since the new rules have recently been applied to our servers, is there a way in SQL Server to calculate when Daylight Savings Time starts and ends for a given year? Are any built-in functions available or do I need to build a custom solution?

Working with strings in SQL Server is not as simple as you would hope for a lot of things that you need to do with text data. It is sometimes simpler to do a lot of these things outside of SQL Server, but if you have time and patience to write T-SQL code you could just about do anything you need to do. One of my programmers asked if there was a way to determine if all characters in a string were capitalized. I was not able to find an existing function so I figured I would create my own. Take a look at this function to see if all characters are capitalized in a string.