Database
Enthusiast

## How do i use ceil and floor value based on miliseconds

How do i select Ceil and Floor value on seconds based on condition?

If the miliconds are less than or equal to 900 miliseconds then use floor value for seconds and if the miliseconds are greater than 900 then use Ceiling value.(See examples below).

 Timestamp Expected TS 08:43:46.710 08:43:46.000 08:42:37.900 08:42:37.000 11:50:06.930 11:50:07.000

5 REPLIES 5
Highlighted

## Re: How do i use ceil and floor value based on miliseconds

CEIL and FLOOR apply only to numeric values.

In your case, it may be easier to add INTERVAL '0.099' SECOND and then truncate the fractional seconds by converting to character and back (CASTs or TO_CHAR/TO_TIMESTAMP).

## Re: How do i use ceil and floor value based on miliseconds

Or without casting to string and back:

```                           (ts + interval '0.099' second)
- cast(extract(second from (ts + interval '0.099' second)) mod 1 as interval second(1,3))
```

Enthusiast

## Re: How do i use ceil and floor value based on miliseconds

Thank you.....Can you please share syntax how to write this cause i am very new to teradata.

Appreciate it.

Enthusiast

## Re: How do i use ceil and floor value based on miliseconds

I used as below but i got the error "Interval field overflow".

,(TIMESTAMP_COLUMN + INTERVAL '0.099' SECOND)
- CAST(EXTRACT(SECOND FROM (TIMESTAMP_COLUMN + INTERVAL '0.099' SECOND)) MOD 1 AS INTERVAL SECOND(1,3))

Enthusiast

## Re: How do i use ceil and floor value based on miliseconds

Thank you and i really appreciate it you help me

I tried different way and it's worked for me (As below):

CASE        WHEN EXTRACT(SECOND FROM TimeStampColumn)<10.00 THEN

CASE WHEN CAST(SUBSTRING(CAST(EXTRACT(SECOND FROM TimeStampColumn) AS VARCHAR(6)) FROM 3 FOR 3) AS INT) <= 900
THEN SUBSTRING(CAST(TimeStampColumn AS VARCHAR(26)) FROM 12 FOR 8)
WHEN CAST(SUBSTRING(CAST(EXTRACT(SECOND FROM TimeStampColumn) AS VARCHAR(6)) FROM 3 FOR 3) AS INT) > 900
THEN SUBSTRING(CAST(TimeStampColumn AS VARCHAR(26)) FROM 12 FOR 5)
||':'||

CAST('0'||OREPLACE(CAST(TRIM(CAST(SUBSTRING(CAST(EXTRACT(SECOND FROM TimeStampColumn) AS VARCHAR(6)) FROM 0 FOR 2) AS INTEGER))+1 AS VARCHAR(3)),'.','') AS VARCHAR(2))
END ||'.000'

WHEN EXTRACT(SECOND FROM TimeStampColumn)>=10.00 THEN

CASE WHEN CAST(SUBSTRING(CAST(EXTRACT(SECOND FROM TimeStampColumn) AS VARCHAR(6)) FROM 4 FOR 3) AS INT) <= 900
THEN SUBSTRING(CAST(TimeStampColumn AS VARCHAR(26)) FROM 12 FOR 8)
WHEN CAST(SUBSTRING(CAST(EXTRACT(SECOND FROM TimeStampColumn) AS VARCHAR(6)) FROM 4 FOR 3) AS INT) > 900
THEN SUBSTRING(CAST(TimeStampColumn AS VARCHAR(26)) FROM 12 FOR 5)
||':'||

CAST( OREPLACE(CAST(TRIM(CAST(SUBSTRING(CAST(EXTRACT(SECOND FROM TimeStampColumn) AS VARCHAR(6)) FROM 1 FOR 2) AS INTEGER))+1 AS VARCHAR(3)),'.','') AS VARCHAR(2))
END
||'.000'
END
) AS VARCHAR(12)) AS ExpectedTimeStamp