how to catch date ?

[xXghostXx]

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 ??

[pritaeas]

What database?

You can use BETWEEN if supported.

[xXghostXx]

@pritaeas oracle databases if that what you meant a

[pritaeas]

http://docs.oracle.com/cd/B28359_01/server.111/b28286/conditions011.htm

[xXghostXx]

@pritaeas thanks :D

[xXghostXx]

@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 ?!!!

[ORAfreak]

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

[ORAfreak]

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

...

[xXghostXx]

when I tried to apply date datatype on orderdate the sqlfiddle.com wont accept
wloud you mind to try it plz ?

[ORAfreak]

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);

[ORAfreak]

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 ;)

[xXghostXx]

Thanks alot :)