Thursday, April 15, 2010

SQL Server Trimming miliseconds (Zero out miliseconds)

Hi,
Where I work we needed to zero out the milliseconds from a datetime field in one of the database. I found out that somebody created function below which returns the passed datetime with the milliseconds zero out. Instead of 2010-04-15 14:50:49.953 this time we needed to return 2010-04-15 14:50:49.000. When I looked at the function I thought there is just to much DATEPART, CONVERT and concatenation being used. And I asked myself If I could make it much shorter, cleaner. So I did come up with a result and it works. Here is the original function:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[TrimMilliseconds] (@D datetime)
RETURNS datetime
AS
BEGIN
DECLARE @Date datetime
SET @Date = CONVERT(datetime,
CONVERT(varchar(4), DATEPART(yyyy, @D)) + '-' +
CONVERT(varchar(2), DATEPART(mm, @D)) + '-' +
CONVERT(varchar(2), DATEPART(dd, @D)) + ' ' +
CONVERT(varchar(2), DATEPART(Hh, @D)) + ':' +
CONVERT(varchar(2), DATEPART(mi, @D)) + ':' +
CONVERT(varchar(2), DATEPART(ss, @D)))

return @Date
END
GO

Here is my updated version:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[TrimMillisecondsNew] (@D datetime)
RETURNS datetime
AS
BEGIN
DECLARE @Date datetime
SET @Date = CAST(CONVERT(VARCHAR(19),@D,20) AS DATETIME)

return @Date
END
GO

As you see the second function is much cleaner and simpler. I only used convert and cast functions once to do the work. Less typing, less t-sql, work is done.
Reblog this post [with Zemanta]

No comments:

Post a Comment