CODEWITHSUNDEEP
dateteradata
John Smith
John Smith

Reputation: 2876

Teradata Convert Integer to a timestamp

I am trying to select data from a table that has a date range Its a bit messy as its being passed from JAVA as an integer of the form 20160101 (YYYYMMDD)

So basically i want to be able to run a query like (incidentally this is the type of time stamp that works in the query)

SELECT * FROM TABLE_A as t
WHERE t_timestamp >='2016-10-01 00:00:00'

I have looked around on google and SO and have come up with something like this

SELECT CAST(20161001 AS DATE FORMAT 'YYYY-MM-DD') || ' 00:00:00'

This is to try and convert the integer number to time stamp that will be acceptable as a date range. This doesn't appear to work. There is no error, just that results for when i statically add the time stamp exist vs when i try and make it general don't exist

Can anyone help?

Upvotes: 0

Views: 7985

Answers (2)

dnoeth
dnoeth

Reputation: 60462

Did you look at the result of SELECT CAST(20161001 AS DATE FORMAT 'YYYY-MM-DD')? It returns '3916-10-01' and you probably don't have data like tghat :-)

Apply a typecast to a string and then cast to a date:

WHERE t_timestamp >= Cast(Trim(20161001) AS DATE Format 'YYYYMMDD')

Upvotes: 1

Kiran
Kiran

Reputation: 3315

If you want to have as character value below query should work sel (CAST('20161001' AS DATE FORMAT 'yyyymmdd')(format 'yyyy-mm-dd')(char(10)))|| ' 00:00:00'

Below query converts the value to timestamp(0)

sel cast((CAST('20161001' AS DATE FORMAT 'yyyymmdd')(format 'yyyy-mm-dd')(char(10)))|| ' 00:00:00' as timestamp(0))

Upvotes: 1

Related Questions