Truncate timestamp

I would like into a stored procedure, truncate timestamp input values at the top hour or at the lower hour.

For example, if my input values are 2020-02-12 06:56:00 and 2020-02-12 07:14:00, I would like to transforme it in 2020-02-12 06:00:00 and 2020-02-12 08:00:00

Is a cast function can work?

You can construct the new datetimes from the parts that you want of your original datetimes.

declare @start datetime = '2020-02-12 06:56:00'
declare @end datetime = '2020-02-12 07:14:00'

select @start as OriginalStart,
       @end as OriginalEnd,
       datetimefromparts(year(@start), month(@start), day(@start), datepart(hour, @start), 0, 0, 0) as TruncatedStart,
       dateadd(hour, 1, datetimefromparts(year(@end), month(@end), day(@end), datepart(hour, @end), 0, 0, 0)) as TruncatedEnd

The first truncation of the interval is the lower hour, and the second one adds an additional hour so it returns the higher hour.

PS: If what you want is to round to the nearest hour, then you can add 30 minutes and truncate :

declare @date datetime = '2020-02-12 06:56:00'

set @date = dateadd(minute, 30, @date)

select datetimefromparts(year(@date), month(@date), day(@date), datepart(hour, @date), 0, 0, 0) as NearestHour

or in a single step (using Lepetit's shortcut for truncation) :

declare @date datetime = '2020-02-12 06:56:00'

select dateadd(hour, datediff(hour, 0, dateadd(minute, 30, @date)), 0) AS NearestHour

Using TRUNC to round down dateTime values, For example, the dateTime attribute TimeStamp has a value representing 10/13/ 2011 11:35:12.000. The list below shows the results of using the TRUNC� The TRUNC_TIMESTAMP function returns a TIMESTAMP WITHOUT TIME ZONE value that is the expression, truncated to the unit that is specified by the format-string.

This is a simpler solution:

declare @start datetime = '2020-02-12 06:56:00' 
declare @end datetime = '2020-02-12 07:14:00'    

select @start as OriginalStart,
       @end as OriginalEnd,
       dateadd(hour, datediff(hour, 0, @start), 0) as TruncatedStart, 
       dateadd(hour, datediff(hour, 0, dateadd(hour, 1, @end)), 0) as TruncatedEnd

In both cases the function substracts the hour part from the original timestamp. For the TruncatedEnd, one hour is added, so that the result is the subsequent hour.

Oracle trunc with timestamp, select TRUNC(TO_DATE('22-AUG-13'), 'YEAR') from dual; select TRUNC(to_timestamp('2013-08-22 06:00:00','YYYY-MM-DD HH24:MI:SS'), 'YEAR') from dual; Both of the above return the same result. The TIMESTAMP data type is an extension of the DATE data type. Truncating a timestamp down to the quarter returns the timestamp corresponding to midnight of the first day of the quarter for the input timestamp. Extracting the quarter date part from a timestamp returns the quarter number of the year in the timestamp.

Using a bit of arithmetic calculation, convert to hours with decimal and use floor() and ceiling() to perform the round up / down

first it find the time different with 00:00:00 in terms of second. convert(date, date_col) convert the datetime to date, so effectively it is 00:00:00

datediff(second, convert(date, date_col), date_col)

then you divide by 60 x 60 = 3600 seconds. Gives you fraction of hours

then you use floor() or ceiling() to perform the rounding

and lastly you add that back to the date (convert(date, date_col))

Final query

select  *,
        RoundDown   = convert(datetime, convert(date, date_col)) 
        + dateadd(hour, floor(datediff(second, convert(date, date_col), date_col) / (3600.0)), 0),
        RoundUp     = convert(datetime, convert(date, date_col))
        + dateadd(hour, ceiling(datediff(second, convert(date, date_col), date_col) / (3600.0)), 0)

from    (
            values 
            ('2020-02-12 06:56:00'),
            ('2020-02-12 07:14:00')
        ) d (date_col)

/*
2020-02-12 06:56:00 2020-02-12 06:00:00 2020-02-12 07:00:00
2020-02-12 07:14:00 2020-02-12 07:00:00 2020-02-12 08:00:00
*/

EDIT : a much simpler query below

find the different in minute divide by 60.0 minutes to get different in terms of hour (with decimal places) and then apply floor or ceiling. Finally add that result back

select  getdate() as Now, 
        dateadd(hour, floor(datediff(minute, 0, getdate()) / 60.0), 0) as RoundDown,
        dateadd(hour, ceiling(datediff(minute, 0, getdate()) / 60.0), 0) as RoundUp

Truncating a timestamp to the second – Learning is not a spectator , SQL> select trunc(localtimestamp,'DD') to_the_day from dual; TO_THE_DA ------- -- 20-OCT-16 1 row selected. But the moment you try apply a� The format argument determines the unit to which the date will be truncated. The format argument is optional. Its default value is DD that instructs the TRUNC () function to truncate the date to midnight. The following table illustrates valid values for the format argument:

Oracle TRUNC Date Function By Practical Examples, Its default value is DD that instructs the TRUNC() function to truncate the date to midnight. The following table illustrates valid values for the format� Just use it in place of TRUNC when ever you want to truncate a timestamp. You won't lose your time zone, and you can specify down to fractional seconds when truncating. Furthermore you can specify the timezone in which the truncation should be performed in.

DATE_TRUNC: A SQL Timestamp Function You Can Count On, Learn how to use Date_Trunc in SQL to round a timestamp to the interval you need. Aggregate time-based data with this helpful function. The date_trunc function truncates a TIMESTAMP or an INTERVAL value based on a specified date part e.g., hour, week, or month and returns the truncated timestamp or interval with a level of precision. The following illustrates the syntax of the date_trunc function:

Truncating a Timestamp, The built-in Vertica function DATE_TRUNC truncates date and time values to the specified precision. The return value is the same data type as� The TRUNC function can be used to round a dateTime value down to a coarser granularity. For example, this may be useful when you want to group your statement results data for each quarter using a dateTime attribute.

Comments
  • can you show some sample data and expected result to illustrate what you want here ?
  • I just edited my question @Squirrel :)
  • How do you decide to round down 06:56 to 06:00 and round up 07:14 to 08:00 ?
  • I have to do a calculation that need to have rounded values of timestamp @Squirrel
  • I tried your query but had this error Date and datetime data types are incompatible in added additions..
  • edited the query. Later version of SQL Server does not likes that.
  • Added a much simpler query