ansaurus

Question

Answer 1

+2 A:

Vincent Malgrat 2009-06-09 14:42:22

Your proposal seems to be very near to the solution I need.On SQL the command is working quiet well, but I need to query it via HQL in hibernate.Using this command hibernate throws following exception:org.hibernate.hql.ast.QuerySyntaxException: expecting CLOSE, found 'DAY'

Develman 2009-06-09 15:14:11

I edited my answer: I've never worked with HQL but maybe it is unaware of the interval datatype, therefore you will have to convert it manually.

Vincent Malgrat 2009-06-09 16:02:12

Unfortunately hibernate is unaware of it.

Develman 2009-06-09 16:12:05

His second solution should work for Hibernate, though - the SQL doesn't require any awareness of the interval datatype, and it's much cleaner than my brute-force string-smashing version. I gave you an upvote because of this.

Steve Broberg 2009-06-09 16:22:20

But your solution is more suitable for my problem, because I have cannot create a HQL-query, but it is created by an underlying framework and in this special case it is quiet complicated to use e.g. subqueries or nested queries like vincents solution.

Develman 2009-06-09 16:47:17

Answer 2

+1 A:

I realize it's not clever at all, nor is it the special format string you're looking for, but this answer does work, given that the ouptut is fixed length:

SELECT    SUBSTR(TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00'))), 1, 1)
       || SUBSTR(TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00'))), 9, 2)
       || ' '
       || SUBSTR(TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00'))), 12, 12)
  FROM table1;

It also just truncs the fractional seconds instead of rounding, but I assume from your example they're all just zeros anyway.

This is an even greater embarrassment, but I couldn't resist:

SELECT SUBSTR(REPLACE(TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00')))
                     , '0000000', '')
             , 1, 16)
  FROM table1;

Steve Broberg 2009-06-09 15:30:33

your second proposal works fine! also with HQL. Thanks a lot for your help!

Develman 2009-06-09 16:04:32

ansaurus

tags:

views:

answers:

format interval with to_char

related questions