Data is Null This method or property cannot be called on Null values error keeps appearing

Question

I dont know why i keep getting getting the error Data is Null. This method or property cannot be called on Null values.
heres my query that will get the sum of all totalovertime between february 18 2016 and february 23 2016 for employeenumber 1:

 select sum(totalovertime) as totalovertime from timeinout where employeenumber = 1 and dateofin between 2016-02-18 and 2016-02-23

heres my table:

 employeenumber | totalovertime | dateofin 
 1              | 1             | 2016-02-19
 1              | 1             | 2016-02-22

the query should return 2 as totalovertime, but the error keeps appearing

but when i do:

select sum(totalovertime) from timeinout where employeenumber = 1

this query returns 2 and thats correct.

Try putting single quotes around the dates: dateofin between '2016-02-18' and '2016-02-23' — Jerrad
– Jerrad, Commented Feb 22, 2016 at 21:48

Jerrad · Accepted Answer · 2016-02-22 21:55:33Z

1

When dealing with dates, you need to surround them with single quotes. Otherwise, SQL will interpret 2016-02-18 as 2016 minus 2 minus 18. Change your query to be

select sum(totalovertime) as totalovertime 
from timeinout 
where employeenumber = 1 
and dateofin between '2016-02-18' and '2016-02-23'

answered Feb 22, 2016 at 21:55

Jerrad

5,3001 gold badge20 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lorence Hernandez Over a year ago

woaahh been fixing this for hours i never forgot putting single quotes until I use "between" in MySQL thanks :)

jwilliams · Accepted Answer · 2016-02-22 21:48:11Z

1

Try using ISNULL() on the value inside the SUM.

SUM(ISNULL(totalovertime, 0))

This checks if totalovertime is null and replaces it with 0 if it is, which the SUM can then handle correctly.

Its also possible that your DateOfIn is NULL for some fields and the BETWEEN operator is throwing your error, not sure if that is possible though (check your table data!)

edited Feb 22, 2016 at 21:48

answered Feb 22, 2016 at 21:43

jwilliams

114 bronze badges

Collectives™ on Stack Overflow

Data is Null This method or property cannot be called on Null values error keeps appearing

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related