Removing the minutes and seconds from a DATETIME

OriginalGriff

4.57/5 (6 votes)

25 Mar 2020CPOL

16K

A basic user defined function to remove minute and second from a DATETIME

This came up in a question about grouping timestamps into two-hour windows and I figured it might be useful to others. So: a basic user defined function to remove the minute and second from a DATETIME. It's then pretty trivial to group them into windows of any length you need.

Using the Code

Open a new query, and paste the code:

SQL

-- =============================================
-- Author:        OriginalGriff (SM/PG)
-- Create date: 2020-03-25
-- Description:    Remove minutes and seconds from a datetime
-- =============================================
CREATE FUNCTION fnStripMinSec 
(
    @DT DATETIME
)
RETURNS DATETIME
AS
BEGIN
    RETURN DATEADD(hour, DATEDIFF(hour, 0, @DT), 0);
END
GO

This creates a simple user defined fucntion you can call at any point in your query:

SQL

SELECT GETDATE() AS [Now], dbo.fnStripMinSec(GETDATE()) AS [Stripped];

Will give you:

Now                        Stripped
2020-03-25 09:36:27.960    2020-03-25 09:00:00.000

History

2020-03-25: First version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)