Home » SQL & PL/SQL » SQL & PL/SQL » Time from Date field (Oracle 8.1.7, Windows XP SP2)
Time from Date field [message #308923] Tue, 25 March 2008 23:36 Go to next message
kumarvk
Messages: 211
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?
Re: Time from Date field [message #308924 is a reply to message #308923] Tue, 25 March 2008 23:44 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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
Re: Time from Date field [message #308927 is a reply to message #308924] Tue, 25 March 2008 23:56 Go to previous messageGo to next message
kumarvk
Messages: 211
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?
Re: Time from Date field [message #308929 is a reply to message #308923] Wed, 26 March 2008 00:11 Go to previous messageGo to next message
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,
Re: Time from Date field [message #308943 is a reply to message #308929] Wed, 26 March 2008 00:51 Go to previous messageGo to next message
kumarvk
Messages: 211
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.
Re: Time from Date field [message #308949 is a reply to message #308923] Wed, 26 March 2008 01:03 Go to previous messageGo to next message
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.
Re: Time from Date field [message #308958 is a reply to message #308943] Wed, 26 March 2008 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Time from Date field [message #308959 is a reply to message #308949] Wed, 26 March 2008 01:35 Go to previous messageGo to next message
kumarvk
Messages: 211
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
Re: Time from Date field [message #308965 is a reply to message #308923] Wed, 26 March 2008 01:44 Go to previous messageGo to next message
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
Re: Time from Date field [message #308971 is a reply to message #308965] Wed, 26 March 2008 01:53 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Thank you very much it works.
Re: Time from Date field [message #308976 is a reply to message #308965] Wed, 26 March 2008 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Wow! Why doing it simple when you can do it complicated! 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
Re: Time from Date field [message #308977 is a reply to message #308976] Wed, 26 March 2008 02:09 Go to previous messageGo to next message
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
Re: Time from Date field [message #308988 is a reply to message #308923] Wed, 26 March 2008 03:05 Go to previous messageGo to next message
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,
Re: Time from Date field [message #308990 is a reply to message #308988] Wed, 26 March 2008 03:19 Go to previous messageGo to next message
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
Re: Time from Date field [message #308996 is a reply to message #308923] Wed, 26 March 2008 03:38 Go to previous messageGo to next message
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,
Re: Time from Date field [message #308998 is a reply to message #308996] Wed, 26 March 2008 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Time from Date field [message #309011 is a reply to message #308923] Wed, 26 March 2008 04:01 Go to previous messageGo to next message
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,
Re: Time from Date field [message #309014 is a reply to message #309011] Wed, 26 March 2008 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Time from Date field [message #309105 is a reply to message #308977] Wed, 26 March 2008 07:38 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Years have 4 digits.
Previous Topic: check the pl/sql function (multiple merges)
Next Topic: Join Query
Goto Forum:
  


Current Time: Sun Dec 04 04:19:51 CST 2016

Total time taken to generate the page: 0.12026 seconds