Issues with returning a dates after 2014

Question

Hi so I'm trying to learn sql and am working on a small exercise. I want to return all the ids from a table that have a date after 2014. Heres what I have:

     CREATE TABLE Pick_Up(
     eid INT, 
     pid INT,
     cid int,
     datetime DATETIME, 
     );
INSERT INTO Pick_Up (eid, pid,  datetime) 
VALUES (3, 7,'2015/4/23 16:35:05'),
       (4, 6,'2016/3/20/ 9:05:45');
SELECT P.pid
FROM pick_up P
WHERE year(p.datetime)> '2014';

And I thought it would return both tuples but when I run it i get nothing. IF anyone could help by telling me what I can do to have it return both values that would be amazingly awesome!

The sample data you posted contains error in datetime data. Secondly, I believe year() function returns integer result so you should get rid of quotes around 2014 in the `where` condition. — Harsh, Mar 29 '16 at 02:37
Your query works for me once I change `p.datetime` to `P.datetime`. Otherwise I get an error. — Barmar, Mar 29 '16 at 03:02
@Barmar What? You have a case sensitive implementation of MySQL ??? — Strawberry, Mar 29 '16 at 06:59
@Strawberry http://stackoverflow.com/questions/6134006/are-table-names-in-mysql-case-sensitive — Barmar, Mar 29 '16 at 15:48

score 0 · Answer 1 · answered Mar 29 '16 at 02:44

0

Only issue is syntax error,there is an extra comma,

SELECT P.pid FROM pick_up P WHERE year(p.datetime)> 2014;

Secondly year is numeric not string.

answered Mar 29 '16 at 02:44

Naruto

4,221
1
21
32

score 0 · Answer 2 · edited Mar 29 '16 at 04:42

0

SELECT P.pid
FROM pick_up P
WHERE year(p.datetime)> 2014   [ year(p.datetime) may result in integer ]

or try something like this

SELECT P.pid
FROM pick_up P
WHERE datepart(yy,p.datetime)> 2014

edited Mar 29 '16 at 04:42

Raviteja

3,399
23
42
69

answered Mar 29 '16 at 04:30

the13thknight

1
1

Issues with returning a dates after 2014

2 Answers2