T-SQL tricks for SQL Server 2012: T-SQL formatting

Editor's note: This is part three of a series of tips on T-SQL tricks for SQL Server 2012 by SQL Server expert Robert Sheldon. This installment examines formatting within expressions. The first part of the series covers "The joy of data values

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at [email protected].

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in August 2012

" and the second part looks at "T-SQL functions." This part of the series will delve into T-SQL formatting.

SQL Server now includes a set of functions that lets you generate date and time values based on individual integers. The first of these is the DATEFROMPARTS function, which takes three arguments -- the year, month and day -- and converts them into a DATE value.

DATEFROMPARTS, TIMEFROMPARTS, DATETIMEFROMPARTS and DATETIME2FROMPARTS

For example, the following SELECT statement uses the DATEFROMPARTS function to generate the DATE value June 14, 2012:

SELECT DATEFROMPARTS(2012, 6, 14);

The function’s first argument, 2012, is the year. The second argument, 6, is the month of June, and the third argument, 14, is the 14th day of that month.

The next function is TIMEFROMPARTS, which takes five arguments -- the hour, minutes, seconds, second fractions and precision. The last argument specifies the precision of the second fractions. The function produces a TIME value based on these five arguments. For example, the following SELECT statement returns the TIME value of 23:04:18.053:

SELECT TIMEFROMPARTS(23, 4, 18, 53, 3);

In this case, the returned TIME value has a precision of three, as specified by the last argument. However, although the fourth argument (the second fractions) specifies only a two-digit integer, the returned value will have three decimal places.

If you want to instead return a DATETIME value, you can use the DATETIMEFROMPARTS function. In this case, you pass in seven arguments, the first three for the date and last four for the time. The DATETIMEFROMPARTS function does not support an argument for the precision. Instead, the precision is always assumed to be 3. For example, the following SELECT statement uses the function to return the DATETIME value 2012-06-14 23:04:18.053:

SELECT DATETIMEFROMPARTS(2012, 6, 14, 23, 4, 18, 53);

As you can see, the function contains seven arguments. The last argument specifies 53 second fractions, but is returned with three decimal places.

If you want to be able to specify the precision, you can use the DATETIME2FROMPARTS function to return a DATETIME2 value. Like the DATETIMEFROMPARTS function, the DATETIME2FROMPARTS function requires the seven arguments necessary to return the date and time, but the function also requires an eighth argument -- the precision -- as shown in the following example:

SELECT DATETIME2FROMPARTS(2012, 6, 14, 23, 4, 18, 53, 3);

Not surprisingly, the function returns the DATETIME2 value of 2012-06-14 23:04:18.053.

EOMONTH

Another interesting function related to dates is EOMONTH, which takes a date expression as an argument and returns the last day of the month specified by that expression. In the following example, the EOMONTH function retrieves the last day of the month based on the current date:

SELECT EOMONTH(GETDATE())

In this case, the date expression is the GETDATE function, which returns the current date and time. Because the date returned by GETDATE is in June 2012, the EOMONTH function returns the last day in June: 2012-06-30.

The EOMONTH function also takes an optional second argument, an integer (plus or minus) that indicates a month after or before the date in the first argument. For instance, the following SELECT statement returns the last day of the month that’s one month after the current month:

SELECT EOMONTH(GETDATE(), 1)

Notice that the second argument is 1, which tells the function to calculate the last day of the next month. Because the GETDATE function returns a June date, the EOMONTH function returns the last day of July: 2012-07-31.

If you want to instead find the last day of a previous month, you can use a negative number as your second argument, as shown in the following example:

SELECT EOMONTH(GETDATE(), -1)

In this case, the EOMONTH function returns the last day of May: 2012-05-31.

SEQUENCE

Starting with SQL Server 2012, you can now define a database object known as a sequence. A sequence provides a mechanism for generating a set of unique numeric values that can be used across the database, rather than being limited to a single table, as is the case with the IDENTITY property. Although you can use the IDENTITY property to generate numbers available throughout the database, the process is a bit cumbersome. A sequence makes all that easier.

To create a sequence in the database, use the CREATE SEQUENCE statement, as shown in the following example:

CREATE SEQUENCE dbo.TestSeq AS INT

START WITH 101

INCREMENT BY 1;

The statement creates a sequence named TestSeq in the dbo schema. The sequence generates INT values, starting with 101, and those values will be incremented by 1. That means the first value generated will be 101, followed by 102, then 103 and so on.

To retrieve a value from a sequence, use the NEXT VALUE FOR clause, as shown in the following example:

SELECT NEXT VALUE FOR dbo.TestSeq;

As you can see, you need only include the sequence name in the clause. The first time you run this statement against the TestSeq sequence, it returns 101. That value will be incremented by 1 each time someone retrieves a value from the sequence. For example, if 1278 was last returned, the next value returned is 1279.

Working with result sets

While it's handy to work with individual values, it can also be useful to work with the result set as a whole so you can take such steps as paging through the results, comparing values from previous rows or returning a specific result in the event of an error.

Let’s look at some of the new Transact-SQL enhancements that let you better control your results.

OFFSET and FETCH NEXT

To make paging your result sets easier, T-SQL now supports the OFFSET and FETCH NEXT options in the ORDER BY clause. Together, they provide the mechanisms necessary to determine where to start paging and how many rows to include in the result set. In the following example, the SELECT statement returns the first 10 rows of the Product table:

DECLARE @offset INT = 0

DECLARE @fetch INT = 10

SELECT ProductID, Name

FROM Production.Product

ORDER BY ProductID

  OFFSET @offset ROWS

  FETCH NEXT @fetch ROWS ONLY;

 

The OFFSET clause specifies the number of rows to skip before starting to return rows. In this case, a 0 is specified (via the @offset variable), so no rows are skipped. However, as the use of the variable demonstrates, you can pass any integer into the clause, which allows you to dynamically determine the number of rows to skip with each pass.

The same is true for the FETCH NEXT clause, which determines the number of rows to return. In this case, that number is 10 (via the @fetch variable), but it can be whatever number is practical, and that number can also be determined dynamically. That means it can change as the variable value changes. The following table shows the results returned by the SELECT statement.

As you can see, 10 rows have been returned. Because the OFFSET clause specifies that no rows should be skipped, these are the first 10 rows in the Product table.

FIRST_VALUE and LAST_VALUE

The FIRST_VALUE function returns the first value from an ordered set of column values, and the LAST_VALUE returns the last value. The functions provide a way to evaluate the values in a specific column to determine which value is first and which is last, according to how those values have been ordered. However, that order is specific to the evaluated column and has nothing to do with how the result set as a whole is sorted.

Let’s look at an example to better understand how these functions work. The following SELECT statement retrieves data from the Product table and uses the FIRST_VALUE and LAST_VALUE functions to compare values to each product’s standard cost:

SELECT ProductNumber, Name, StandardCost,

  FIRST_VALUE(StandardCost) OVER(ORDER BY StandardCost) AS LowestCost,

  LAST_VALUE(StandardCost) OVER(ORDER BY StandardCost

    RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS HighestCost

FROM Production.Product

WHERE ProductNumber LIKE 'bk-M%48';

The first three items returned by the SELECT list are simply the ProductNumber, Name and StandardCost columns. The fourth item is a column expression that uses the FIRST_VALUE function to determine which StandardCost amount is the lowest. The function takes a single argument, in this case, the StandardCost column, which means a value from this column is what will be displayed in the result set. The FIRST_VALUE function also requires an OVER clause that specifies the column to be evaluated and the order of the values in that column. In other words, the function will sort the StandardCost column (in ascending order, by default) and retrieve the first value, which is the lowest amount.

The LAST_VALUE function works much the same way, except that it retrieves the last value, which is the highest amount. However, the OVER clause in this function requires an additional argument -- RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- to ensure that the last value is returned for each row. (For more details about this option, as well as information about other aspects of these two functions, refer to SQL Server Books online.)

Because the FIRST_VALUE and LAST_VALUE functions are used to define the column expressions in the SELECT list, the result set includes those columns, and each row lists the lowest and highest amounts, along with the regular column information. The following table shows the results returned by the SELECT statement.

If you review the StandardCost column, you’ll see that the lowest value is 294.5797 and the highest value is 1912.1544. These are the two values displayed in each row of the LowestCost and HighestCost columns, respectively.

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top