CODEWITHSUNDEEP
dateteradatarounding
user3471254
user3471254

Reputation: 1

How to round current time in teradata and insert into timestamp(6) fields

I have a table with date fields of timestamp(6) fields . 

create table test_time 
(
  t1  timestamp(6)  format 'mm/dd/yyyy hh:mm:si' ,
);

I want to insert into this table with current date and time rounded.
i.e. say for example if the current date time is 08/07/2014 10:34:56 then the value in the table should be 08/07/2014 10:00:00 .
(or) if current data and time is 08/07/2014 10:54:56 then also the value should be
08/07/2014 10:34:56

Upvotes: 0

Views: 1724

Answers (2)

Swethan
Swethan

Reputation: 1

select
cast('2059-02-19 10:59:59.110000' as timestamp(6)),  
CASE WHEN EXTRACT(MINUTE FROM cast('2059-02-19 10:59:59.110000' as timestamp(6))) - 60   >= 0
THEN  cast('2059-02-19 10:59:59.110000' as timestamp(6)) + ((30 - EXTRACT(MINUTE FROM cast('2059-02-19 10:59:59.110000' as timestamp(6)))) * INTERVAL  '1' MINUTE) 
- (EXTRACT(SECOND FROM cast('2059-02-19 10:31:59.110000' as timestamp(6))) * INTERVAL '1' SECOND)
ELSE cast('2059-02-19 10:59:59.110000' as timestamp(6)) -  EXTRACT(MINUTE FROM cast('2059-02-19 10:59:59.110000' as timestamp(6)))  * INTERVAL  '1' MINUTE
 - (EXTRACT(SECOND FROM cast('2059-02-19 10:31:59.110000' as timestamp(6)))* INTERVAL '1' SECOND)    
END AS Converted_Time

Upvotes: 0

dnoeth
dnoeth

Reputation: 60462

Your first example is truncating the time, not rounding.

Truncating can be done like this:

CURRENT_TIMESTAMP(0) - 
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP(0)) * INTERVAL '1' MINUTE - 
EXTRACT(SECOND FROM CURRENT_TIMESTAMP(0)) * INTERVAL '1' SECOND

But I don't get your second example, there's no truncation/rounding at all, it's simply subtracting 20 minutes?

Upvotes: 1

Related Questions