hey
I have table that has date column and I want to select just all the sales that hapen in the first three month between 1/1/2012 to 30/3/2012
How I am able to do this select query ??
hey
I have table that has date column and I want to select just all the sales that hapen in the first three month between 1/1/2012 to 30/3/2012
How I am able to do this select query ??
What database?
You can use BETWEEN
if supported.
@pritaeas oracle databases if that what you meant a
@pritaeas thanks :D
@pritaeas and everyone wirte this code
at this site Click Here
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderName varchar(255) NOT NULL,
OrderDate varchar(255)
);
insert into persons values ('1','sara','lala','street10','de');
insert into persons values ('2','toto','no','street10','nl');
insert into persons values ('3','momo','yes','street10','ch');
insert into persons values ('4','bobo','yea','street10','us');
insert into persons values ('5','malk','tata','street10','sw');
insert into persons values ('6','yuo','zoo','street10','UK');
insert into Orders values ('1','order1','29/5/200');
insert into Orders values ('2','order2','7/6/2000');
insert into Orders values ('3','order3','9/9/2001');
insert into Orders values ('4','order4','22/3/2002');
What I want to do is to select the orders that are hapened in 2001 which is order3 here HOW ?!!!
Hi,
As I can see as a bit unusual you use Varchar datatype for OrderDate.
Anyway, if this is OK for you, select is very simple and should looks like:
SQL> select * from orders where orderdate like'%2001';
O_ID
----------
ORDERNAME
--------------------------------------------------------------------------------
ORDERDATE
--------------------------------------------------------------------------------
3
order3
9/9/2001
Also you can create OrderDate varchar(255) -> OrderDate date
It will give you more manipulation possibility...
Now I will create another table orders_chng with same data and similar structure (will use date instead of varchar) and do select:
SQL> create table orders_chng as select O_ID,ORDERNAME,to_date(ORDERDATE,'dd/mm/yyyy') as ORDERDATE from orders;
Table created.
SQL> select * from orders_chng where to_char(orderdate,'YYYY')='2001';
O_ID
----------
ORDERNAME
--------------------------------------------------------------------------------
ORDERDAT
--------
3
order3
09.09.01
And changed table structure is:
SQL> desc orders_chng
Name Null? Type
----------------------------------------- -------- ----------------------------
O_ID NOT NULL NUMBER(38)
ORDERNAME NOT NULL VARCHAR2(255)
ORDERDATE DATE
date manipulations and output:
SQL> select ORDERNAME, to_char(ORDERDATE,'DD-MM-YY HH24:MI') formated from orders_chng;
ORDERNAME FORMATED
-------------------- --------------
order1 29-05-00 00:00
order2 07-06-00 00:00
order3 09-09-01 00:00
order4 22-03-02 00:00
SQL> select ORDERNAME, to_char(ORDERDATE,'IW') calendar_week from orders_chng;
ORDERNAME CA
-------------------- --
order1 22
order2 23
order3 36
order4 12
Or just get orders from some specific years:
SQL> select ORDERNAME from orders_chng where to_char(orderdate,'YYYY') in ('2000','2002');
ORDERNAME
--------------------
order2
order4
...
when I tried to apply date datatype on orderdate the sqlfiddle.com wont accept
wloud you mind to try it plz ?
I did my example on real Oracle DB, anyway sqlfiddle.com works fine if you select oracle 11gR2 from menu
and create table with following create table statement:
CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderName varchar(255) NOT NULL,
OrderDate date);
Also inserts must looks like:
insert into Orders values ('1','order1',to_date('29/05/2000','dd/mm/yyyy'));
insert into Orders values ('2','order2',to_date('7/6/2000','dd/mm/yyyy'));
insert into Orders values ('3','order3',to_date('9/9/2001','dd/mm/yyyy'));
insert into Orders values ('4','order4',to_date('22/3/2002','dd/mm/yyyy'));
commit;
Do not forget to commit once when running in oracle otherwise your transaction will remains open and visible only in your session ;)
Thanks alot :)
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.