SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Reporting by date - DateTrunc

By Mike Tanner,

SQL Server has DateAdd and DateDiff functions, but lacks a DateTrunc function to truncate a date to some time interval (or multiple thereof).  Using DateTrunc is a convenient way of e.g. listing sales by week or month.

Whilst the SQL fragment to do the truncation is simple, if subtle, I find I can never remember the syntax and always need to look it up.

The code supplied provides a simple and easy-to-remember function for doing this.  An example of use might be as follows, to return a count of jobs by quarter:

select dbo.DateTrunc('q', JobDate, 1) as qtr, count(*)  as cnt
from Job 
group by dbo.DateTrunc('q', JobDate, 1) 
order by 1
Whilst, as a function, it is slower than the equivalent code [dateadd(qq, datediff(qq, 0, JobDate) , 0) in this case] it is fine for ad-hoc use on all but the largest tables.  And it provides a convenient place to store the definitions to save looking it up on the web each time even if you usually use the explicit variant.

The final parameter, @Num, is useful if, for example, you wanted the number of calls for every 5 minutes or 15 seconds or whatever.

It works and is tested in SQL Server 2005.

The stored procedure is based on code in the following article, https://stackoverflow.com/questions/2639051/what-is-the-best-way-to-truncate-a-date-in-sql-server, but with a tweak for the seconds calculation (where there is a danger of overflow).

Total article views: 334 | Views in the last 30 days: 2
 
Related Articles
FORUM

SQL Server 2008 - Log Truncation

SQL Server 2008 - Log Truncation

SCRIPT

Function to Round or Truncate DateTime

This Function is used to round to Second, Minute, Hour or Day or to Truncate to Second, Minute, Hou...

SCRIPT

Truncate Table Partition command in SQL Server

Procedure to automatically truncate table partition

FORUM

Is it wrong to use Truncate or DBCC command inside a function

Is it wrong to use Truncate or DBCC command inside a function

FORUM

truncate permission

truncate permission

Tags
 
Contribute