Link to home
Start Free TrialLog in
Avatar of carlino70
carlino70Flag for Argentina

asked on

Calculate range of column with DATE format

Hi, experts:

I need a function to obtain from the next string:
'08/10/2013 06:27:02','dd/mm/yyyy hh24:mi:ss'

Open in new window

two values DATE:
'08/10/2013 03:00:00','dd/mm/yyyy hh24:mi:ss

Open in new window

'
and
'07/10/2013 03:00:00','dd/mm/yyyy hh24:mi:ss'

Open in new window

Could you help me with an example, please?

Thanks, regards!
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of carlino70

ASKER

ok, I'll try this.
Thanks
It works with:
select
      trunc(to_date('08/10/2013 06:27:02','dd/mm/yyyy hh24:mi:ss'),'DD') + 3/24,
      (trunc(to_date('08/10/2013 06:27:02','dd/mm/yyyy hh24:mi:ss'),'DD') + 3/24)-1
from dual; 

Open in new window

But when I add him a name of column:
select min(utctime) minimo,
      (trunc(to_date(min(utctime),'dd/mm/yyyy hh24:mi:ss'),'DD') + 3/24)-1 DESDE,
      trunc(to_date(min(utctime),'dd/mm/yyyy hh24:mi:ss'),'DD') + 3/24 HASTA
from a_1dia_001;

Open in new window

I see:
MINIMO|DESDE|HASTA
28/09/2011|27/09/0011 03:00:00 a.m.|28/09/0011 03:00:00 a.m.
Why 0011 ? Should I add a year too?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Confirm the format of the raw data.

My guess is that it doesn't match the format mask of: 'dd/mm/yyyy hh24:mi:ss'

The string values need to match the format mask 100%.
It works with this format:

select min(utctime)+3/24, trunc(min(utctime),'dd') +1 +3/24 hasta, trunc(min(utctime),'dd')-1 +3/24 desde 
from a_1dia_001;  

Open in new window

I see;
MIN(UTCTIME)|HASTA|DESDE
28/09/2011|29/09/2011 03:00:00 a.m.|27/09/2011 03:00:00 a.m.

Open in new window


I think is OK for me, Thanks!