Tip 8: Date Functions
Requirement: You need to retrieve daily order information and summarize it by week for a weekly report or weekly graph.
The two brief code snippets below demonstrate how to use SQL Server's DATEPART
function to solve this requirement. The first snippet presents a UDF that converts any date during the week to a Saturday date for that week. The second snippet demonstrates the UDF as part of a query against orders, which summarizes the data by the week-ending Saturday date. You can use this result set for a weekly report or graph. This represents another example where you can use Transact SQL to prepare a result set with little or no data munging in the business layer.
|In constructing an UPDATE trigger, you need to remember that the trigger fires once, regardless of the number of rows affected by an UPDATE statement.|
CREATE FUNCTION dbo.GetEndOfWeek
-- Converts date to the Saturday date for the week
RETURNS DateTime AS
DECLARE @dRetDate DateTime
SET @dRetDate =
@dDate + ( 7-DATEPART(weekday,@dDate))
SELECT dbo.GetEndOfWeek(OrderDate) AS WeekEnding,
SUM(Amount) AS WeekAmount
GROUP BY dbo.GetEndOfWeek(OrderDate)
returns an integer based on the portion of the date that you want to analyze. "SQL Server Books Online
" lists all of the date portions (dateparts) that the DATEPART
function can analyze. For example, you could use the quarter
datepart to summarize data by quarter.
datepart is scoped to SQL Server's setting for the first day of the week. The default is Sunday. To set the first day of the week to a different day (e.g., summarize sales from Monday to Sunday instead of Sunday to Saturday), use the SET DATEFIRST
-- Change first day of week from default of
-- Sunday (7) to Monday (1)
SET DATEFIRST 1
One final note on dates: in many instances, you might build queries that compare dates where the date column has a time other than 12 AM, yet the date comparison variable is a date with no time defined.
For example, a row with a date of "05-26-2003 22:10:00" will not be retrieved if the query calls for all rows where Date <= CAST('05-26-2003') AS DATETIME
. This is because SQL compares 5-26-2003 at 22:10:00 to 5-26-2003 at 00:00:00 (12 AM), and finds that the former is not <= the latter. If you want all data through 5-26, including any transactions that hit right up to 11:59 PM that day, you could add one day to the comparison date, and change the logic to less than (Date < CAST('05-26-2003') + 1 )
Tip 9: Data Conversions with CAST and CONVERT
Requirement: You need to produce an English-like message that describes the results of an accounting process. The string should read something like "100 Employee Checks were generated on 11/15/2004 at 5:07:02 PM, for a total dollar value of $134,123.11".
SQL Server provides the functions CAST
that allow you to make different data types work together. Listing 8
demonstrates how to use variables of different data types (date, decimal) to build a message string that could be used for a log or display.
Tip 10: Update Triggers
Requirement: You need to make sure that any UPDATES performed against a table automatically update a timestamp column.
trigger is a specific type of stored procedure that fires every time an UPDATE
statement executes against the table. You can use triggers to enforce specific rules, and/or to ensure that specific columns are updated.
triggers provide access to two critical system tables that contain the state of the row before it was updated (DELETED
) and after it was updated (INSERTED
). You can use the contents of these tables to implement basic audit trail functionality, which I'll cover in Tip #11. For now, I'll concentrate on setting up an automatic update of a LASTUPDATED
In constructing an UPDATE
trigger, you need to remember that the trigger fires once, regardless of the number of rows affected by an UPDATE
statement. Those new to SQL Server sometimes make the mistake of writing trigger code with the assumption that only one row will be updated.
CREATE TRIGGER Upd_Client ON dbo.Client
SET LastUpdate = GETDATE()
FROM Client C
JOIN Inserted I
ON I.PrimaryKey = C.PrimaryKey