Teradata Convert DATE to midnight

Question

Below is the original code

convert(datetime,
            (substring(convert(varchar(26),
            convert(datetime,getdate())),1,12) +
            '23:59:59:996'))

output is = 6/17/2015 11:59:59.996

I converted it to Teradata below

CAST(CURRENT_TIMESTAMP AS DATE FORMAT 'MM/DD/YYYY') || ' 23:59:59:999990'

but upon compiling my stored procedure, it produces an error below

Invalid or missing expression 'E(5404):Datetime field overflow.'.

Is there a command in Teradata to set the day into midnight?

Thanks

Answer 1

When you cast a date to a tamestamp it's set to midnight, then you can add or subtract an interval:

-- tomorrow minus a millisecond
CAST(CURRENT_DATE +1 AS TIMESTAMP(3)) - INTERVAL '0.001' SECOND

-- or today plus almost 24 hours
CAST(CURRENT_DATE AS TIMESTAMP(3)) + INTERVAL '23:59:59.999' HOUR TO SECOND

Using PRIOR there's a simplified version where you don't have to think about centi/milli/microseconds:

-- tomorrow minus one unit of time (based on the input datatype)
Prior(Cast(Current_Date+1 AS TIMESTAMP(3)))