Home » SQL & PL/SQL » SQL & PL/SQL » BETWEEN (oracle 10.1.2)
BETWEEN [message #330854] Tue, 01 July 2008 08:45 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi all

Can i use BETWEEN for two columns which are dates

for example
WHERE CONTRACT_TYPE='On call' and CONTRACT_TYPE BETWEEN SLECTION_DATE AND FRIMSIGN
Re: BETWEEN [message #330856 is a reply to message #330854] Tue, 01 July 2008 08:46 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
aarti81 wrote on Tue, 01 July 2008 06:45
Hi all

Can i use BETWEEN for two columns which are dates

for example
WHERE CONTRACT_TYPE='On call' and CONTRACT_TYPE BETWEEN SLECTION_DATE AND FRIMSIGN


Yes, if all 3 variables really are DATE datatypes
Re: BETWEEN [message #330858 is a reply to message #330854] Tue, 01 July 2008 08:49 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
aarti81 wrote on Tue, 01 July 2008 09:45
Hi all

Can i use BETWEEN for two columns which are dates

for example
WHERE CONTRACT_TYPE='On call' and CONTRACT_TYPE BETWEEN SLECTION_DATE AND FRIMSIGN


No!
How can CONTRACT_TYPE be equal to 'On call' and also be between two dates?
What happens when you try it? Couldn't you do that before asking us what would happen?
Re: BETWEEN [message #330861 is a reply to message #330858] Tue, 01 July 2008 08:53 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
joy_division wrote on Tue, 01 July 2008 08:49
aarti81 wrote on Tue, 01 July 2008 09:45
Hi all

Can i use BETWEEN for two columns which are dates

for example
WHERE CONTRACT_TYPE='On call' and CONTRACT_TYPE BETWEEN SLECTION_DATE AND FRIMSIGN


No!
How can CONTRACT_TYPE be equal to 'On call' and also be between two dates?
What happens when you try it? Couldn't you do that before asking us what would happen?

Sorry i didnt mention it, but i got the following message:
ORA-00900 invalid SQL statement

Cause: The statement is not recognized as a valid SQL statement. This error can occur if the Procedural Option is not installed and a SQL statement is issued that requires this option (for example, a CREATE PROCEDURE statement). You can determine if the Procedural Option is installed by starting SQL*Plus. If the PL/SQL banner is not displayed, then the option is not installed.

Action: Correct the syntax or install the Procedural Option.

and what i wanted was the contract_type should be oncall and at the same time it must be between the two date fields 'selection_date and firmsign'
thanks
Re: BETWEEN [message #330862 is a reply to message #330854] Tue, 01 July 2008 08:55 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow Posting Guidelines below
http://www.orafaq.com/forum/t/88153/0/
Re: BETWEEN [message #330863 is a reply to message #330862] Tue, 01 July 2008 09:00 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
anacedent wrote on Tue, 01 July 2008 08:55
Please read & follow Posting Guidelines below
http://www.orafaq.com/forum/t/88153/0/

did i say something wrong? or was i rude, as far as my most is concerned i was polite and was thankful.If you can help please do else dont sit here judging people.
Re: BETWEEN [message #330864 is a reply to message #330861] Tue, 01 July 2008 09:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
WHERE CONTRACT_TYPE='On call' and CONTRACT_TYPE BETWEEN SLECTION_DATE AND FRIMSIGN

This is not a valid statement, it misses at least SELECT and FROM clauses.

And as joy_division said: "How can CONTRACT_TYPE be equal to 'On call' and also be between two dates?".
How could a variable be a string and a date?

Regards
Michel
Re: BETWEEN [message #330865 is a reply to message #330864] Tue, 01 July 2008 09:19 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Michel Cadot wrote on Tue, 01 July 2008 09:12
Quote:
WHERE CONTRACT_TYPE='On call' and CONTRACT_TYPE BETWEEN SLECTION_DATE AND FRIMSIGN

This is not a valid statement, it misses at least SELECT and FROM clauses.

And as joy_division said: "How can CONTRACT_TYPE be equal to 'On call' and also be between two dates?".
How could a variable be a string and a date?

Regards
Michel


Soory sir
But i want to get in the output all the contract_types that are on call and they should be between these two date fields
selection_date and firmsign.

thnank you
Re: BETWEEN [message #330866 is a reply to message #330865] Tue, 01 July 2008 09:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As we only have what you posted that is no information about your table and its columns, how could you expect we can give an accurate answer?
What should be between these 2 dates? Birth date of contractor?

Regards
Michel

Re: BETWEEN [message #330944 is a reply to message #330854] Tue, 01 July 2008 21:24 Go to previous messageGo to next message
sreenu80
Messages: 50
Registered: July 2006
Location: bangalore
Member

Hi,

Could you please provide some sample data of your table.

what is the data types and of the CONTRACT_TYPE,SLECTION_DATE AND FRIMSIGN.

please provide the sample data like

select CONTRACT_TYPE,SLECTION_DATE,FRIMSIGN from table;

sreenu Smile

[Updated on: Tue, 01 July 2008 21:27]

Report message to a moderator

Re: BETWEEN [message #331026 is a reply to message #330944] Wed, 02 July 2008 03:16 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
please provide the sample data like

select CONTRACT_TYPE,SLECTION_DATE,FRIMSIGN from table;

Rather than supplying the data as the output of a SELECT, supply the data as create table and insert scripts. That way, other people can actually reproduce the scenario on theirown databases and be able to help much more easily.
Previous Topic: Help in Pivot Query (Error ORA-00937: not a single-group group function)
Next Topic: PL/SQL
Goto Forum:
  


Current Time: Sun Dec 04 04:36:12 CST 2016

Total time taken to generate the page: 0.20369 seconds