carlino70
asked on
Calculate range of column with DATE format
Hi, experts:
I need a function to obtain from the next string:
and
Thanks, regards!
I need a function to obtain from the next string:
'08/10/2013 06:27:02','dd/mm/yyyy hh24:mi:ss'
two values DATE:'08/10/2013 03:00:00','dd/mm/yyyy hh24:mi:ss
'and
'07/10/2013 03:00:00','dd/mm/yyyy hh24:mi:ss'
Could you help me with an example, please?Thanks, regards!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It works with:
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?
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;
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;
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?
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%.
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%.
ASKER
It works with this format:
I think is OK for me, Thanks!
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;
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.
I think is OK for me, Thanks!
ASKER
Thanks