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
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
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
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
ceiling() to perform the rounding
and lastly you add that back to the date (
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
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.
- 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:00and round up
- 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 Serverdoes not likes that.
- Added a much simpler query