NULL result in MySQL querrie.

Anon

I made following table,

create table myProjectRole (
myProjectRoleID smallint unsigned primary key auto_increment not null,
fkEmployeeID smallint unsigned not null REFERENCES Employee,
ProjectRoleStart date,
ProjectRoleEstimatedEnd date,
ProjectRoleEnd date,
ProjectRoleBooking float(1,2));

INSERT INTO myProjectRole VALUES ('','1',’1999-09-23’,’1999-11-30’,’’,’25’);
INSERT INTO myProjectRole VALUES ('','1',’1999-10-23’,’2001-11-30’,’’,’25’);
INSERT INTO myProjectRole VALUES ('','1',’2000-09-23’,’’,’2002-03-23’,’75’);
INSERT INTO myProjectRole VALUES ('','1',’2001-09-23’,’2003-11-30’,’’,’10’);
INSERT INTO myProjectRole VALUES ('','1',’2001-09-23’,’’,’2002-03-23’,’25’);
INSERT INTO myProjectRole VALUES ('','1',’2002-05-23’, ’’ ,’2003-11-30’,’10’);

I want to go in day by day and check Booking but as soon as I start using date functions in my where querries I get NULL as a result. What am I doing wrong?

SELECT sum(ProjectRoleBooking) as Booking
FROM myProjectRole
WHERE fkEmployeeID='1';

gives the result 170.

SELECT sum(ProjectRoleBooking) as Booking
FROM myProjectRole
WHERE to_days(2001-08-27)>=to_days(ProjectRoleStart)
AND to_days(2001-08-27)<= to_days(ProjectRoleEnd)
AND fkEmployeeID='1'
AND DAYOFWEEK(2001-08-27)<7
AND DAYOFWEEK(2001-08-27)>1;

gives the result NULL.

[deleted]

First, never every use smallint or tinyint in an auto_increment field. allways use int.

Second, if you don't get what you think you should be getting, simplify the query and print all the results.
leave out the sum(), and just select ProjectRoleBooking

Third, When comaparing dates, you don't have to convert it using to_days().

Fourth, Numbers should never be quoted, only strings are quoted.

crislewis

Dates in mysql are in the form YYYYMMDD not YYYY-MM-DD. Have you checked that the dates you intended to go into the rows are in fact stored as you expect?

Also, shouldn't the dates in the functions be specifies as strings? i.e.

DAYOFWEEK('2001-08-27')

The same goes for to_days(). See section 6.3.4 of the mysql manual:

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Date_and_time_functions

Cheers,

Cris

crislewis

Ignore the date format bit. I was confusing Date with Timestamp.

Sorry,

Cris

Anon

I took away to_days() and added '' round my date and got it to work.

Thanks Vincent and Cris.

SELECT sum(ProjectRoleBooking) as Booking FROM myProjectRole
WHERE '2001-08-27'>=ProjectRoleStart
AND '2001-08-27'<= ProjectRoleEnd
AND fkEmployeeID=1
AND DAYOFWEEK('2001-08-27')<7
AND DAYOFWEEK('2001-08-27')>1;

gives the result 75