Count differs while using the date values

Question

Count differs while using the date values

pstanand

SSC Rookie

Points: 33
More actions
April 28, 2016 at 6:45 am

#318565

Hi,
I'm an Oracle developer and new to SQL Server. I ran the following queries in SQL Server 2008 and got different results.
Could you please advise me which one is correct and to follow?
select count(1) from sap_plant
where lastupdate between '2016-04-19 00:00:01' and '2016-04-27 23:59:59'
Record Count 11634
select count(1) from sap_plant
where lastupdate >='2016-04-19'
and lastupdate <'2016-04-27'
Record Count 15057
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '2016-04-19'
SET @EndDate = '2016-04-27'
SELECT count(1)
FROM sap_plant
WHERE lastupdate BETWEEN @StartDate AND @EndDate + '23:59:59'
Record Count 15879
Thanks
Regards,
pstanand

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

The operator BETWEEN is inclusive. The common way to do this is to write the range. Also, for using only dates, the ISO 8601 short format is preferred.

select count(1) from sap_plant

where lastupdate >='20160419'

and lastupdate <'20160428'; --One day after the end of the range to include the whole day.

--Or using variables

DECLARE @StartDate DateTime;

DECLARE @EndDate DateTime;

SET @StartDate = '20160419';

SET @EndDate = '20160427';

SELECT count(1)

FROM sap_plant

where lastupdate >= @StartDate

and lastupdate < @EndDate + 1; --No need to change the value from the 'parameter'

Your first query is missing all the values equal to 2016-04-19 00:00:00 and greater than '2016-04-27 23:59:59' but lower than '2016-04-28'. So basically, two full seconds.

In the second one, you're losing almost a whole day.

In the third one, you're missing one second.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

pstanand SSC Rookie Points: 33 More actions · Answer 2

Hi,

thanks for your information. Could you please provide me the correct query as I'm not aware of SQL Server queries.

Thanks

Regards,

pstanand

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 3

Did the queries Luis provided not work? What happened?

An alternative would be to change your parameters to DATE instead of DATETIME, then use BETWEEN as you have (without the TIME addition). If 'lastupdate' is DATETIME you would need to convert this to DATE in the WHERE clause.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

pstanand (4/28/2016)
Hi,
thanks for your information. Could you please provide me the correct query as I'm not aware of SQL Server queries.
Thanks
Regards,
pstanand

I did provide 2 queries which should be correct. The first uses literal values, while the other one uses variables which could be turn into parameters in a stored procedure.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

tindog (4/28/2016)
Did the queries Luis provided not work? What happened?
An alternative would be to change your parameters to DATE instead of DATETIME, then use BETWEEN as you have (without the TIME addition). If 'lastupdate' is DATETIME you would need to convert this to DATE in the WHERE clause.

Usually, I'm against converting data types in columns. This could be an exception because converting from datetime to date is still SARGable. I don't recommend it because people might think that this is fine with any data type and will cause performance problems.

As a general rule, always use the column's data type for comparisons.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 6

Luis Cazares (4/28/2016)
tindog (4/28/2016)
Did the queries Luis provided not work? What happened?
An alternative would be to change your parameters to DATE instead of DATETIME, then use BETWEEN as you have (without the TIME addition). If 'lastupdate' is DATETIME you would need to convert this to DATE in the WHERE clause.
Usually, I'm against converting data types in columns. This could be an exception because converting from datetime to date is still SARGable. I don't recommend it because people might think that this is fine with any data type and will cause performance problems.
As a general rule, always use the column's data type for comparisons.

A good point, thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Luis Cazares (4/28/2016)
SELECT count(1) FROM sap_plant where lastupdate >= @StartDate and lastupdate < @EndDate + 1; --No need to change the value from the 'parameter'

I'd recommend against using that "date variable + 1" form of code. While it works for Datetime variables, it won't for the newer datetime2, datetimeoffset or date datatypes. It's brittle code, someone changes the variable/parameter datatypes and suddenly the code breaks.

Rather use DATEADD.

DECLARE @StartDate DateTime2;

DECLARE @EndDate DateTime2;

SET @StartDate = '20160419';

SET @EndDate = '20160427';

SELECT count(1)

FROM sys.objects

where create_date >= @StartDate

and create_date < @EndDate + 1;

Msg 206, Level 16, State 2, Line 7

Operand type clash: datetime2 is incompatible with int

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pstanand SSC Rookie Points: 33 More actions · Answer 8

Hi,

So shall I use the below one? Suggest me.

select count(1) from sap_plant

where lastupdate >='20160419'

and lastupdate <'20160428';

Thanks

Regards,

pstanand

Sergiy SSC Guru Points: 110206 More actions · Answer 9

pstanand (4/28/2016)
Hi,
So shall I use the below one? Suggest me.
select count(1) from sap_plant
where lastupdate >='20160419'
and lastupdate <'20160428';
Thanks
Regards,
pstanand

Yes.

That would be correct.

If you use paramerters you may wish to user following construction:

where lastupdate >=@StartDate

and lastupdate < DATEADD(dd, 1, @EndDate);

@EndDate here would be the last date to be included into the query: 27 Apr 2016

_____________
Code for TallyGenerator