Home » SQL & PL/SQL » SQL & PL/SQL » select date
select date [message #202919] Mon, 13 November 2006 05:08 Go to next message
oracle_coorgi
Messages: 188
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
iam unable to select only record for 07Nov06
if i use between i get records eg:07 november ,2006-0941

Select
TO_CHAR(session_START_time,'dd month,yyyy-hh24mi') LOG_IN_TIME,
TO_CHAR(session_END_time,'dd month,yyyy-hh24mi') LOG_OUT_TIME
from SESSION_LOG
where SESSION_LOG.SESSION_START_TIME
between '06Nov06' AND '07Nov06';

thanxs

Report message to a moderator

Re: select date [message #202937 is a reply to message #202919] Mon, 13 November 2006 06:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
USE DATES NOT STRINGS!!!!

and,

USE FOUR DIGIT YEARS!!!!

Try this
where trunc(SESSION_LOG.SESSION_START_TIME) 
 = to_date('07Nov2006'','ddMonyyyy');

Report message to a moderator

Re: select date [message #202946 is a reply to message #202937] Mon, 13 November 2006 07:07 Go to previous messageGo to next message
oracle_coorgi
Messages: 188
Registered: September 2006
Location: INDIA-karnataka
Senior Member
IT GIVES ERROR
in java we r using this string

BETWEEN TO_DATE('" + fromDate + "', 'ddMonyyyy') AND TO_DATE('" + toDate + "', 'ddMonyyyy')

if any one want the data for the same day eg:07-NOV-2006 then

pls do the needful

Report message to a moderator

Re: select date [message #202949 is a reply to message #202946] Mon, 13 November 2006 07:12 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Which error you are getting?

Can you please post entire sql statement with single quotes?
They really do matter.

I didn't understand where the single quotes have started and ended.


By
Vamsi

Report message to a moderator

Re: select date [message #202953 is a reply to message #202946] Mon, 13 November 2006 07:19 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
oracle_coorgi
BETWEEN TO_DATE('" + fromDate + "', 'ddMonyyyy') AND TO_DATE('" + toDate + "', 'ddMonyyyy')

Are you sure we all are speaking Oracle here? Should + (plus) sign, perhaps, be || (double pipe, concatenation operator)?

Report message to a moderator

Re: select date [message #202978 is a reply to message #202946] Mon, 13 November 2006 08:09 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
oracle_coorgi wrote on Mon, 13 November 2006 08:07

if any one want the data for the same day eg:07-NOV-2006 then

pls do the needful



I do not understand this statement.

Report message to a moderator

Re: select date [message #202993 is a reply to message #202919] Mon, 13 November 2006 08:33 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member
Post your piece of java code and small test data. Then it is possible to understand the problem

Report message to a moderator

Re: select date [message #203009 is a reply to message #202937] Mon, 13 November 2006 09:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ooops. Typo. One quote too many.
Should be 
where trunc(SESSION_LOG.SESSION_START_TIME) 
 = to_date('07Nov2006','ddMonyyyy');

Report message to a moderator

Re: select date [message #203016 is a reply to message #203009] Mon, 13 November 2006 10:03 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
JRowbottom wrote on Mon, 13 November 2006 09:24
Ooops. Typo. One quote too many.
Should be 
where trunc(SESSION_LOG.SESSION_START_TIME) 
 = to_date('07Nov2006','ddMonyyyy');



Just in case session_start_time is indexed, you can avoid a full table scan with the following

where SESSION_LOG.SESSION_START_TIME between  to_date('07Nov2006','ddMonyyyy') AND to_date('07Nov2006','ddMonyyyy')+ (86366/86400) ...;

[Updated on: Mon, 13 November 2006 10:03]

Report message to a moderator

Re: select date [message #203090 is a reply to message #203016] Mon, 13 November 2006 19:27 Go to previous messageGo to next message
oracle_coorgi
Messages: 188
Registered: September 2006
Location: INDIA-karnataka
Senior Member
In JAVA we r using this string

BETWEEN TO_DATE('" + fromDate + "', 'ddMonyyyy') AND TO_DATE('" + toDate + "', 'ddMonyyyy')

oracle syntax THIS IS CORRECT
select
TO_CHAR(session_START_time,'dd month,yyyy-hh24mi') LOG_IN_TIME,
TO_CHAR(session_END_time,'dd month,yyyy-hh24mi') LOG_OUT_TIME
FROM CATS_USER , SESSION_LOG WHERE
SESSION_LOG.SESSION_START_TIME
BETWEEN '01Nov06' AND '06Nov06';
i get data from '01 november ,2006-1021' to '06 november ,2006-1021'
Iam unable to get WITH THIS QUERY
select
TO_CHAR(session_START_time,'dd month,yyyy-hh24mi') LOG_IN_TIME,
TO_CHAR(session_END_time,'dd month,yyyy-hh24mi') LOG_OUT_TIME
FROM CATS_USER , SESSION_LOG WHERE
SESSION_LOG.SESSION_START_TIME
BETWEEN '07Nov06' AND '07Nov06';
fromdate '07 november ,2006-1021' todate '07 november ,2006-1021' that is same date

iwant to select fromdate and todate with same date eg
fromdate='07nov2006' and todate='07nov2006'

thanxs

Report message to a moderator

Re: select date [message #203150 is a reply to message #203090] Tue, 14 November 2006 02:03 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The problem seems to be that your dates in SESSION_LOG.SESSION_START_TIME have a time component stored with them, that you're not taking account of in your WHERE clause.

(I'm assuming that when you write "07 november ,2006-1021" you mean "10:21AM on 7th Nov, 2006")

The clause BETWEEN '07Nov06' AND '07Nov06' (which REALLY should be BETWEEN to_Date('07Nov06','ddMonYY') AND to_date('07Nov06','ddMonYY') - you do know that '07Nov06' is a string and not a date, don't you?) will only match one date, and that is TO_DATE('07-Nov-2006 00:00:00','dd-Mon-yyyy hh24:mi:ss') ie midnight on the 7th of Nov

If you want to fetch all the columns with a date on the 7th of november you cand do the following (2 of which were suggested in the email just above your last one)
where trunc(SESSION_LOG.SESSION_START_TIME) = to_date('07Nov2006','ddMonyyyy');
where SESSION_LOG.SESSION_START_TIME between  to_date('07Nov2006','ddMonyyyy') AND to_date('07Nov2006','ddMonyyyy')+ (86366/86400)
where SESSION_LOG.SESSION_START_TIME >= to_date('07Nov2006','ddMonyyyy') and SESSION_LOG.SESSION_START_TIME < to_date('08Nov2006','ddMonyyyy')

Report message to a moderator

Previous Topic: unique rows
Next Topic: Rollback
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Dec 10 02:52:01 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.