Click here to Skip to main content
14,494,314 members

Removing the minutes and seconds from a DATETIME

Rate this:
4.57 (6 votes)
Please Sign up or sign in to vote.
4.57 (6 votes)
24 Mar 2020CPOL
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:

-- =============================================
-- 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:

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)

Share

About the Author

OriginalGriff
CEO
Wales Wales
Born at an early age, he grew older. At the same time, his hair grew longer, and was tied up behind his head.
Has problems spelling the word "the".
Invented the portable cat-flap.
Currently, has not died yet. Or has he?

Comments and Discussions

 
GeneralMy vote of 5 Pin
dmjm-h26-Mar-20 12:51
Memberdmjm-h26-Mar-20 12:51 
QuestionSimpler solution? Pin
basementman26-Mar-20 8:44
Memberbasementman26-Mar-20 8:44 
Suggestion(exc)use-case please Pin
Eek Ten Bears25-Mar-20 4:06
MemberEek Ten Bears25-Mar-20 4:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Tip/Trick
Posted 24 Mar 2020

Tagged as

Stats

2.6K views
2 bookmarked