converting timestamp to day(4) to minute

Question

converting timestamp to day(4) to minute

bablu123 (TechnicalUser)

(OP)

11 Mar 10 19:32

Hi guys,
Iam converting teradata sql to Pl/sql
In teradata i have
select * from table
where condition
AND ( ( time1 - time2 ) DAY ( 4 ) TO MINUTE ) >= '0 00:01.

so the above query has to work in oracle

can any one help me out

thanks in advance

r937 (TechnicalUser) · Answer 1 · 2010-03-11 22:53:34

ANSI SQL is probably not going to help you, i suggest you post your question in an oracle forum

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

JarlH (Programmer) · Answer 2 · 2010-03-12 02:35:12

The ANSI SQL way is:

select * from table
where condition
AND ( ( time1 - time2 ) DAY ( 4 ) TO MINUTE ) >= INTERVAL '0 00:01' DAY(4) TO MINUTE

But, as r937 already said, you'd better ask this question in an Oracle forum. Date/time handling is one of the worst areas when it comes to ANSI compliance. Too many vendors have their own, non-standard, implementations.

bablu123 (TechnicalUser) · Answer 3 · 2010-03-12 12:57:33

Actually when i try to use day(4)to minute in Oracle Pl/Sql it won't work

But iam gone with day(4)to second like this

AND ( ( time1 - time2 ) DAY ( 4 ) TO SECOND ) >= '0 00:01:60.

iam not sure this is the way doing.

jaxtell (Programmer) · Answer 4 · 2010-03-12 13:34:46

I think you want this

CODE

select * from table where ((time1 - time2) DAY TO SECOND) >= interval '+00 00:01:00' day to second

-----------------------------------------
I cannot be bought. Find leasing information at http://www.joshaxtell.com/

bablu123 (TechnicalUser) · Answer 5 · 2010-03-12 13:55:36

Hi Jaxtell

actually in teradata we donot use INTERVAL
we just
( ( time1 - time2 ) DAY ( 4 ) TO MINUTE ) >= '0 00:01.

i think it will work.
can you explain me why you are using interval..

( ( time1 - time2 ) DAY ( 4 ) TO SECOND ) >= '0 00:00:60.'

you can check teradata query here

http://www.teradataforum.com/l081007a.htm

jaxtell (Programmer) · Answer 6 · 2010-03-12 14:39:26

I'm using oracle. Isn't that what you're trying to convert to? My understanding is that by specifying interval I am making an explicit conversion instead of an implicit one. But it will probably work without specifying interval since you can use the default day and seconds precision on the right side.

-----------------------------------------
I cannot be bought. Find leasing information at http://www.joshaxtell.com/

Come Join Us!

Posting Guidelines

converting timestamp to day(4) to minute