OraFAQ Forum: SQL & PL/SQL » Time from Date field

Home » SQL & PL/SQL » SQL & PL/SQL » Time from Date field (Oracle 8.1.7, Windows XP SP2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Time from Date field [message #308923]

Tue, 25 March 2008 23:36

kumarvk
Messages: 214
Registered: July 2004

Senior Member

Hi,

I have the following sql like this

select rno,rdate from job_table where
RODATE BETWEEN To_date('01-MAR-08 08:30:00','dd-mon-yy hh24:mi:ss') AND
To_date('26-MAR-08 11:00:00','dd-mon-yy hh24:mi:ss')

Actually I want to extract only the reocrds for all the days which has time between 8.30-11.00 only but I am getting all the records having in between these dates. If I give single date then I am able to get between 8.30-11.00 but I want for all the days between 8.30-11.00

Can anyone help me?

Report message to a moderator

Re: Time from Date field [message #308924 is a reply to message #308923]

Tue, 25 March 2008 23:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>but I want for all the days between 8.30-11.00
so do not specify date portion only hour & minute portion

Report message to a moderator

Re: Time from Date field [message #308927 is a reply to message #308924]

Tue, 25 March 2008 23:56

kumarvk
Messages: 214
Registered: July 2004

Senior Member

Hello,

I want between these dates and between these hours. I tried but cannot. Can you help me with the correct sql?

Report message to a moderator

Re: Time from Date field [message #308929 is a reply to message #308923]

Wed, 26 March 2008 00:11

mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi

Senior Member

the date and hour condition must be separated in two conditions.

always post what you have done and what is current problem.

regards,

Report message to a moderator

Re: Time from Date field [message #308943 is a reply to message #308929]

Wed, 26 March 2008 00:51

kumarvk
Messages: 214
Registered: July 2004

Senior Member

Hi,

I did like this also

select rno,rdate from job_table where
RODATE BETWEEN To_date('01-MAR-08 08:30:00','dd-mon-yy hh24:mi:ss') AND
To_date('26-MAR-08 11:00:00','dd-mon-yy hh24:mi:ss')
and rodate between to_date('08:30:00', 'hh24:mi:ss') and
to_date('11:00:00','hh24:mi:ss')

But the output result is wrong.

Report message to a moderator

Re: Time from Date field [message #308949 is a reply to message #308923]

Wed, 26 March 2008 01:03

mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi

Senior Member

again you have not described what is wrong and not posting in desire format till.

regards.

Report message to a moderator

Re: Time from Date field [message #308958 is a reply to message #308943]

Wed, 26 March 2008 01:31

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In your opinion what does mean "rodate between to_date('08:30:00', 'hh24:mi:ss') and to_date('11:00:00','hh24:mi:ss')"?

Regards
Michel

Report message to a moderator

Re: Time from Date field [message #308959 is a reply to message #308949]

Wed, 26 March 2008 01:35

kumarvk
Messages: 214
Registered: July 2004

Senior Member

Hi,

I get the result like this only, if I use my sql.

RO NO RO DATE
RA0101236 01-03-08 8:52
RA0101239 01-03-08 9:20
RA0101241 01-03-08 10:07
RA0101251 01-03-08 10:52
RA0101237 01-03-08 9:14
RA0101238 01-03-08 9:20
RA0101240 01-03-08 9:24
RA0101242 01-03-08 10:09
RA0101243 01-03-08 10:15
RA0101244 01-03-08 10:20
RA0101246 01-03-08 10:27
RA0101247 01-03-08 10:31
RA0101248 01-03-08 10:39
RA0101249 01-03-08 10:42
RA0101250 01-03-08 10:49
RA0101252 01-03-08 10:53
RA0101245 01-03-08 10:21

Report message to a moderator

Re: Time from Date field [message #308965 is a reply to message #308923]

Wed, 26 March 2008 01:44

mintomohan
Messages: 104
Registered: November 2006
Location: India

Senior Member

hi,

could you please try this one:

SELECT rno, rdate
  FROM job_table
 WHERE rodate BETWEEN TO_DATE ('01-MAR-08 08:30:00', 'dd-mon-yy hh24:mi:ss')
                  AND TO_DATE ('26-MAR-08 11:00:00', 'dd-mon-yy hh24:mi:ss')
   AND TO_CHAR (SYSDATE, 'DD-MON-YY ') || TO_CHAR (rodate, 'HH24:MI:SS')
          BETWEEN    TO_CHAR (SYSDATE, 'DD-MON-YY ')
                  || TO_CHAR (TO_DATE ('01-MAR-08 08:30:00',
                                       'dd-mon-yy hh24:mi:ss'
                                      ),
                              'HH24:MI:SS'
                             )
              AND    TO_CHAR (SYSDATE, 'DD-MON-YY ')
                  || TO_CHAR (TO_DATE ('26-MAR-08 11:00:00',
                                       'dd-mon-yy hh24:mi:ss'
                                      ),
                              'HH24:MI:SS'
                             );

Minto

Report message to a moderator

Re: Time from Date field [message #308971 is a reply to message #308965]

Wed, 26 March 2008 01:53

kumarvk
Messages: 214
Registered: July 2004

Senior Member

Thank you very much it works.

Report message to a moderator

Re: Time from Date field [message #308976 is a reply to message #308965]

Wed, 26 March 2008 01:59

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Wow! Why doing it simple when you can do it complicated! Smile

Smile

Do you realize that:

 TO_CHAR (TO_DATE ('01-MAR-08 08:30:00',
                                       'dd-mon-yy hh24:mi:ss'
                                      ),
                              'HH24:MI:SS'

is just '08:30:00'?

Why concatenating the current day and not just checking "TO_CHAR (rodate, 'HH24:MI:SS')" is between the hours?

Regards
Michel

Report message to a moderator

Re: Time from Date field [message #308977 is a reply to message #308976]

Wed, 26 March 2008 02:09

mintomohan
Messages: 104
Registered: November 2006
Location: India

Senior Member

you are right Michel,

i think this this one will serve the purpose:

SELECT rno, rodate
  FROM job_table
 WHERE rodate BETWEEN TO_DATE ('01-MAR-08 08:30:00', 'dd-mon-yy hh24:mi:ss')
                  AND TO_DATE ('26-MAR-08 11:00:00', 'dd-mon-yy hh24:mi:ss')
   AND TO_CHAR (rodate, 'HH24:MI:SS') BETWEEN '08:30:00' AND '11:00:00';

Minto

Report message to a moderator

Re: Time from Date field [message #308988 is a reply to message #308923]

Wed, 26 March 2008 03:05

mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi

Senior Member

in first condition it is not neccessary to include time as we are using time in second condition.

regards,

Report message to a moderator

Re: Time from Date field [message #308990 is a reply to message #308988]

Wed, 26 March 2008 03:19

mintomohan
Messages: 104
Registered: November 2006
Location: India

Senior Member

if we don't give the time in first condition, i think we will miss out those values coming in the duration 26-MAR-08 00:00 to 11:00

Minto

Report message to a moderator

Re: Time from Date field [message #308996 is a reply to message #308923]

Wed, 26 March 2008 03:38

mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi

Senior Member

Quote:

i think we will miss out those values coming in the duration 26-MAR-08 00:00 to 11:00

i donot think so.

regards,

Report message to a moderator

Re: Time from Date field [message #308998 is a reply to message #308996]

Wed, 26 March 2008 03:49

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes, it will miss them.
If you don't give hour, 00:00:00 is taken:

SQL> select to_date('01/03/2008','DD/MM/YYYY') from dual;
TO_DATE('01/03/2008
-------------------
01/03/2008 00:00:00

1 row selected.

Regards
Michel

Report message to a moderator

Re: Time from Date field [message #309011 is a reply to message #308923]

Wed, 26 March 2008 04:01

mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi

Senior Member

but i mean to sort only date(dd-mm-yyyy) from first condition and time from second condition.

regards,

Report message to a moderator

Re: Time from Date field [message #309014 is a reply to message #309011]

Wed, 26 March 2008 04:03

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You want to apply a trunc on the first condition?
This will prevent the use of an index (if it exists).
The previous query is better.

Regards
Michel

Report message to a moderator

Re: Time from Date field [message #309105 is a reply to message #308977]

Wed, 26 March 2008 07:38

Frank
Messages: 7901
Registered: March 2000

Senior Member

Years have 4 digits.

Report message to a moderator

Previous Topic:	check the pl/sql function (multiple merges)
Next Topic:	Join Query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jul 12 13:48:55 CDT 2025