Timestamp problems, 'HH:MM') > '09:01' 1

[Larshg]

Hi

I have a sql(made with help fra this thread220-903346) that select the number of minutes a timestamp is after 9:00

there are 2 problems, I seem to get the times where the job is pricisly 9:00 and also the ones where the hours is 00:

This is my SQL
select ETL_PROGRAM.program_name, to_char(ETL_PROGRAM_log.program_end, 'yyyy-mm--dd-dy hh24:mi'),
to_char(ETL_PROGRAM_log.program_end, 'HH24')*60 + to_char(ETL_PROGRAM_log.program_end, 'MI') - 540 as minutes
from ETL_PROGRAM_log, ETL_PROGRAM
where ETL_PROGRAM_log.program_id = ETL_PROGRAM.program_id
and ETL_PROGRAM.program_name = 'END_CYCLE'
and to_char(ETL_PROGRAM_log.program_end, 'YYYYMM')='200401'
and to_char(ETL_PROGRAM_log.program_end, 'DY') != 'SAT'
and to_char(ETL_PROGRAM_log.program_end, 'DY') != 'SUN'
and to_char(ETL_PROGRAM_log.program_end, 'HH:MM') > '09:01'

And this is my Output
END_CYCLE, 2004-01--02-fre 00:59, -481
END_CYCLE, 2004-01--02-fre 12:31, 211
END_CYCLE, 2004-01--08-tor 09:00, 0

The first line should not be there, becouse it is only 00:59 witch is way before 9:00
And the last line should not either becourse it is at 09:00

Thanks
Larshg

 

[PruSQLer]

It apparently doesn't like your > '09:01' statement. Don't know what your RDMS is but mine is DB2 and I would do this

and HOUR(etl_program_log.program_end) > 9

Does your platform have an HOUR function? If so, this should work. If not, sorry.

 

[PruSQLer]

Whoops, you'd also have to put this in the code:

and MINUTE(etl_program_log.program_end) > 0

 

[fredericofonseca]

PruSQLer

That code would remove 10:00, 11:00 12:00 and so on.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fredericofonseca]

larshg


the code should be to_char(timestamp,'HH:MI') > '09:00'

it should use MI not MM. My mistake on the previous thread.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Larshg]

Changing ti MM to MI solvede one of the issues - the one with 09:00 (should probaly have spottede that one my self, but thanks)

But the problem with 'HH' '00'
END_CYCLE, 2004-01--02-fre 00:59, -481
Is still coursing a problem.

I've tryede adding, but without result.
and to_char(ETL_PROGRAM_log.program_end, 'HH') != '00'

I'm working on a ORACLE 9i database on HP-UX server

/Larshg

 

[fredericofonseca]

I'm with Oracle 10g but his should not matter.

Sorry had not noticed the negative one.

to_char(timestamp,'HH24:MI') > '09:00'

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Larshg]

Thanks I works like a charm.

/Larshg