Search code examples
sql-serverdatetimedatediff

How to format a date with my own time part in SQL

I'm working on a SQL query which returns a integer which is the number of minutes between two given dates as follows

DATEDIFF(mi, date_one, getdate())

The above query returns difference in two dates in minutes but for getdate() I would want to supply my own time.

For example, consider 

date_one= 2015-12-29 13:39:03.000
getdate() return current date and time ie., 2015-12-29 14:33:50.000

But, I want to change time part in getdate() to some 10:00:00.00 so that the getdate() is 2015-12-29 10:00:00.00 by passing an hour integer say 10.

May I know a good way to do that?

Solution

  • I find this function useful:

    CREATE FUNCTION [dbo].[StripTimeFromDateTime] 
(
    @date DateTime
)
RETURNS DateTime
AS
BEGIN

    RETURN DATEADD(dd, DATEDIFF(dd, 0, @date), 0)

END

    This will knock the time off a datetime leaving it at 00:00:00.000. Then you can:

    SELECT DATEADD(hour, 10, dbo.StripTimeFromDateTime(GetDate()))