Different result for one query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Different result for one query

Author

Topic

v_yaduvanshi
Starting Member

11 Posts

Posted - 2014-08-07 : 09:10:51

Hi,

Today i got a very surprising issue with SQL 2008, my one team member inform that a single query is resulting two different data set from two diff-diff SSMS's query windows.

Query is like this,

Select * from table1 where trandate between '2014-08-02 00:00:00.000' and '2014-08-03 23:59:59.999'

in one query window its showing 295 records while on same desktop in second query window its showing 602 records.

Please let me know where is problem.

Thanking you.
Virendra Yaduvanshi
Principal DBA,
MCTS, MCITP
http://wikidba.wordpress.com

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-07 : 09:22:23

How frequently is table1 being updated? That is, could it have changed between the two queries? If not, there's no way to debug the problem without the actual data.

Here's a tip when comparing dates. Don't use BETWEEN. Instead write it like this:


select * from table1 where trandate >= '2014-08-02' and trandate < '2014-08-04'

This helps to avoid weird corner conditions and is shorter to write.

v_yaduvanshi
Starting Member

11 Posts

Posted - 2014-08-07 : 09:26:43

Data are not being update/insert/delete for table1,
Its showing in one windows n records and in second its showing n+1 records, if u run quary at a time from 2 qry window.

quote:
Originally posted by gbritton

How frequently is table1 being updated? That is, could it have changed between the two queries? If not, there's no way to debug the problem without the actual data.

Here's a tip when comparing dates. Don't use BETWEEN. Instead write it like this:

select * from table1 where trandate > '2014-08-02' and trandate < '2014-08-04'

This helps to avoid weird corner conditions and is shorter to write.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-07 : 10:53:38

Well then, I don't think we can debug this for you without the data

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-07 : 12:59:22

Maybe a trigger is inserting a row upon select?
Just a thought.

v_yaduvanshi
Starting Member

11 Posts

Posted - 2014-08-07 : 21:08:27

Box is in an ideal position... no trigger/updation/deletion/insertion....

Virendra Yaduvanshi
MCTS, MCITP SQL Server
http://wikidba.wordpress.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-07 : 21:48:03

There isn't any magic. The person must be connected to two different servers, two different databases or have a date format setting that's different between the two windows.

Run a trace to see what's going on.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources