I’m not going to say much, the quoted comment from below explains it. Oracle pampers us.
From http://www.bennadel.com/blog/122-Getting-Only-the-Date-Part-of-a-Date-Time-Stamp-in-SQL-Server.htm
“I know I’m late to the party, but having dealt with both SQL Server and Oracle at length, it’s easy to say that date handling and manipulation in SQL Server leaves much to be desired. As evidenced by this thread, truncating dates in SQL Server oftentimes involves massive amounts of function nesting and string concatenation. Oracle offers a function named “trunc”, which does the same thing in a neat little package. The group I work for oftentimes deals with massive amounts of data, so we are all the time summarizing that data by day/month/quarter or even year in some cases. Remembering all the hoops to go through is just a mess, so I wrote a SQL Server equivalent to Oracle’s TRUNC function.
The function works like this:
fn_trunc(p_date,period)
For instance, if I want to truncate getdate() to today’s date:
select fn_trunc(getdate(),’d')
or the first day of this month:
select fn_trunc(getdate(),’m')
.. and so on. fn_trunc will truncate any date to the nearest minute, hour, day, month, quarter or year. The period argument accepts the same arguments the SQL Server DATEPART function uses, as well as the equivalent arguments used in the Oracle TRUNC function.
I’ll paste the code below, and please note that this implementation is for a system-wide function. Also note that I have not exhaustively tested the performance of this function call, so please anyone chime in if you have discovered a quicker way to skin this cat. I stayed away from string concatenation where possible, but prefer that over things like multiple DATEPART calls.
<code>
USE master
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
EXEC sp_configure ‘allow updates’, 1
RECONFIGURE WITH OVERRIDE
GO
IF EXISTS (SELECT *
FROM dbo.sysobjects
WHERE uid = USER_ID(‘system_function_schema’)
AND name = ‘fn_trunc’)
DROP FUNCTION system_function_schema.fn_trunc
GO
/* ALL SYSTEM-WIDE FUNCTIONS MUST BEGIN WITH “fn_”"AND
MUST BE ALL lowercase
*/
CREATE FUNCTION system_function_schema.fn_trunc
(
@p_date datetime,
@p_period varchar(4) = ‘d’
)
returns smalldatetime
as
begin
declare @l_date smalldatetime
set @l_date =
case
when @p_period in (‘n’,'mi’)
then cast(convert(varchar(16),@p_date,120) as smalldatetime)
when @p_period in (‘h’,'hh’)
then cast(convert(varchar(14),@p_date,120)+’00′ as smalldatetime)
when @p_period in (‘d’,'dd’)
then cast(cast(@p_date as varchar(11)) as smalldatetime)
when @p_period in (‘w’,'wk’,'ww’)
then dateadd(dd, 1 – datepart(dw, convert(varchar(10), @p_date, 111)),convert(varchar(10), @p_date, 111))
when @p_period in (‘m’,'mm’)
then cast(convert(char(6),@p_date,112)+’01′ as smalldatetime)
when @p_period in (‘q’,'qq’)
then cast(cast(year(@p_date) as varchar(4)) + ‘/’ + cast(datepart(q, @p_date) * 3 – 2 as varchar(2)) + ‘/01′ as smalldatetime)
when @p_period in (‘y’,'yy’,'yyyy’)
then cast(convert(char(4),@p_date,120)+’-01-01′ as smalldatetime)
else
cast(cast(@p_date as varchar(11)) as smalldatetime)
end
return @l_date
end
go
EXEC sp_configure ‘allow updates’, 0
RECONFIGURE WITH OVERRIDE
GO
</code>
“
Leave the first comment ▶