How to catch part of date ?

[xXghostXx]

What I want to do is to select the orders that are hapened in 2001 ONLY which is "order3" here whats the SQL statment shlould be written HOW I can catch the year part from this full date ???

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
FirstName varchar(255),
LastName varchar(255) NOT NULL,
Address varchar(255),
City varchar(255) DEFAULT 'unknown'
);

CREATE TABLE Orders
(
O_Id int NOT NULL,
P_Id int,
OrderName varchar(255),
OrderDate varchar (255),
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
);

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','sy');
insert into persons values ('5','malk','tata','street10','sy');
insert into persons (P_Id,FirstName,LastName,Address) values ('6','yuo','zoo','street10');


insert into Orders values ('1','2','Order1','29/5/200');
insert into Orders values ('2','1','Order2','7/6/2000');
insert into Orders values ('3','2','Order3','9/9/2001');
insert into Orders values ('4','1','Order4','22/3/2002');

[pritaeas]

Are you still mainly referring to Oracle? If so, please post your next questions in that forum, or state it clearly in your question.

Can you please explain why you are using a 'varchar' column for a date (instead of 'date')?

[xXghostXx]

@pritaeas yea man but I am using SQL not oracle... and im using varchar instead of date coz the website u provide to me http://www.sqlfiddle.com wont accept date as keyword

I had tried this but its not working and I am getting errors

OrderDate datetime DEFAULT GETDATE(),

I am still beginner so please be Patient and help me to get through my problems no matter how its stupid please man :(

[pritaeas]

Even in SqlFiddle you'll have to select a server. If you set it to Oracle then you can use DATE as column type.

It should work in MySQL or MSSQL. So if you have a problem, please post your full query, the server you are using (because the are SQL differences between them) and the error message ("it's not working" is just too vague). Without those it's just difficult to answer correctly.

[xXghostXx]

I tried to select oracle, MySQl 5.5.28 server with the above code
the error message is
"Schema Creation Failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'getdate(),
ordercost int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Pers' at line 6: "

and I am looking for the query that list orders that happened in 2001 ONLY how ??

[pritaeas]

DEFAULT GETDATE() works for MSSQL, but not for MySQL.

I am looking for the query that list orders that happened in 2001 ONLY how

In MySQL you can use:

SELECT * FROM orders WHERE YEAR(OrderDate) = 2001

[xXghostXx]

uh nice nice and how about the time data type sqlfiddle wont accept it as keyword

OrderDate time,

and about the query u had typed as future if are these two query right ??

SELECT * FROM orders WHERE month(OrderDate) = 3
SELECT * FROM orders WHERE day(OrderDate) = 18

and is that works if i want to select the month and year ?

    SELECT * FROM orders WHERE month year(OrderDate) = 3 2001

if there is something to read about this idea ???

[pritaeas]

1. You forgot to say which server you are trying. It should wprk on MySQL.

2. Yes, they are right.

3. Should be this:

   SELECT * FROM orders WHERE MONTH(OrderDate) = 3 AND YEAR(OrderDate) = 2001

[xXghostXx]

first of all thanks alot for being Patient
I am working on that site sqlfiddle.com I tried "date","time", and "datetime" keywords on oracle,and MySQl 5.5.28 server

thanks thanks thanks :))

anything you could advice me to read about nested SQL statment ??
i tried this site but it questions is not really understoodble from me
http://www.sqlzoo.net/wiki/AdventureWorks

and i found it not very useful
thanks

[pritaeas]

Hard to think of something. Just make some tables and experiment.

This is what I had to do last week, perhaps you can research that. I have the following table:

galleries_photos
----------------
id int (pk autoinc)
gallery_id int (fk)
order int
filename varchar(255)
title varchar(255)

Sample data
order by gallery_id, order
-----------
1,1,1,dsc001.jpg,yellow
2,1,2,dsc002.jpg,brown
3,1,3,dsc003.jpg,red
4,2,1,dsc006.jpg,null
5,2,2,dsc005.jpg,null
6,2,3,dsc004.jpg,null

What I want is an update query that changes the order of gallery_id 1. The new order should be sorted by title. So after you run the query your output must be:

New data
order by gallery_id, order
-----------
2,1,1,dsc002.jpg,brown
3,1,2,dsc003.jpg,red
1,1,3,dsc001.jpg,yellow
4,2,1,dsc006.jpg,null
5,2,2,dsc005.jpg,null
6,2,3,dsc004.jpg,null

[xXghostXx]

ummmm I create the table but i did not clearlly understand what the query you want the only changes I saw is the order of title column... here is what i have done and the query as I understand it even if its not working since its unique constraint which cant be changed

create table mytable
(
id int not null primary key,
gallery_id int not null,
orders int,
filename varchar(255),
title varchar(255) default 'null',  
FOREIGN KEY (gallery_id) REFERENCES mytable(id)
); 

insert into mytable (id,gallery_id,orders,filename,title) values ('1','1','1','dsc001.jpg','yellow');
insert into mytable (id,gallery_id,orders,filename,title) values ('2','1','2','dsc002.jpg','brown');
insert into mytable (id,gallery_id,orders,filename,title) values ('3','1','3','dsc003.jpg','red');
insert into mytable (id,gallery_id,orders,filename) values ('4','2','1','dsc006.jpg');
insert into mytable (id,gallery_id,orders,filename) values ('5','2','2','dsc005.jpg');
insert into mytable (id,gallery_id,orders,filename) values ('6','2','3','dsc004.jpg');

update mytable set id=1 where filename='dsc002.jpg';
update mytable set id=2 where filename='dsc003.jpg';
update mytable set id=3 where filename='dsc001.jpg';

[pritaeas]

I did not mean 3 queries. I meant one single update query (updating the order, not the id).

Why did you rename the column order to orders?

[xXghostXx]

uh ill work on it and I used orders insted of order becouse order is keyword

[pritaeas]

becouse order is keyword

;) that was supposed to be a catch, but it is possible to use it.

[xXghostXx]

hahahahhaha i catch it and get through it ;)
and as I told u ill work on it and let know

[pritaeas]

I meant you can use this:

create table mytable
(
    id int not null primary key,
    gallery_id int not null,
    `order` int,
    filename varchar(255),
    title varchar(255)
); 

[xXghostXx]

thanks man and to be honest im not able to write the query u ask me for

[pritaeas]

I don't have the query with me, but you can find a similar question/answer in this SO thread.