Problem with the datetime - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with the datetime

Author

Topic

muniappank
Starting Member

7 Posts

Posted - 2010-05-17 : 14:21:51

I have the following 3 sql queries.
First SQL query: Selecting stock received between start date and end date. (1/16/2010 to 1/16/2010 11:59:59 PM)
Second SQL Query: Stock qty received after end date (1/16/2010 11:59:59 PM)
Third SQL Query: Stock Qty received after start date (1/16/2010)

If the first query is returning 2 and the second query is returning 68 then, the 3rd query should return 70. But it is returning 68.

Thank you for your reply.

SQL Queries,

SELECT SUM(Quantity) AS BtwnQuantity FROM View_Inventory_In INNER JOIN Inventory ON
View_Inventory_In.ItemNum = Inventory.ItemNum and View_Inventory_In.Store_Id = Inventory.Store_Id
WHERE Inventory.Count_This_Item = 1 AND Inventory.ItemType IN (0,13) AND [DateTime] BETWEEN '1/16/2010 12:00:00 AM' AND '1/16/2010 11:59:59 PM'
AND TransType NOT IN ('S','V','K') AND Inventory.Store_Id in ('595') AND Inventory.ItemNum IN ('045100004503')

SELECT SUM(Quantity) AS BtwnQuantity FROM View_Inventory_In INNER JOIN Inventory ON
View_Inventory_In.ItemNum = Inventory.ItemNum and View_Inventory_In.Store_Id = Inventory.Store_Id
WHERE Inventory.Count_This_Item = 1 AND Inventory.ItemType IN (0,13) AND [DateTime] > '1/16/2010 11:59:59 PM' AND TransType NOT IN ('S','V','K')
AND Inventory.Store_Id in ('595') AND Inventory.ItemNum IN ('045100004503')

SELECT SUM(Quantity) AS BtwnQuantity FROM View_Inventory_In INNER JOIN Inventory ON
View_Inventory_In.ItemNum = Inventory.ItemNum and View_Inventory_In.Store_Id = Inventory.Store_Id
WHERE Inventory.Count_This_Item = 1 AND Inventory.ItemType IN (0,13) AND [DateTime] > '1/16/2010' AND TransType NOT IN ('S','V','K')
AND Inventory.Store_Id in ('595') AND Inventory.ItemNum IN ('045100004503')

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-17 : 15:06:17

What data values for [DateTime] for the two rows are you not getting? Do they happen to fall on '1/16/2010 00:00:000'?

Maybe you need to change the > to >= ??

muniappank
Starting Member

7 Posts

Posted - 2010-05-17 : 15:18:26

Results
=======
First SQL Query:2
Second SQL Query:68
Third SQL Query:68
I tried with ">=". But it is returning the same. The third sql query should return 70. And also I tried with '1/16/2010 00:00:000' and '1/16/2010 12:00:00 AM', It is returning 68 only.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-17 : 15:25:11

Understood. Again, what are the two DateTime values that you expect to be returned but are not returned by the third query?

muniappank
Starting Member

7 Posts

Posted - 2010-05-17 : 15:29:19

Below is the quantities returned, If I am not using SUM
-9.99999999979997E+15
-100000
-99999
-790
-103
-103
-103
-103
-103
-103
-103
-103
-103
-103
-103
-103
-103
-47
-36
-16
-15
-14
-10
-10
-9
-7
-7
-1
-1
-1
-1
-1
1
1
1
2
2
2
3
13
14
16
16
36
36
66
99
103
103
103
103
103
103
103
103
103
103
103
103
103
103
197
395
1E+16

muniappank
Starting Member

7 Posts

Posted - 2010-05-17 : 15:50:31

quote:
Originally posted by Lamprey

Understood. Again, what are the two DateTime values that you expect to be returned but are not returned by the third query?

I am getting only count. I am not expecting any datetime to be returned
Thank you

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-17 : 16:55:04

I guess you'll need to post some sample data and expected output. Here is a link to help you put that together:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

If you select DateTime and Quantity for each query that might point you in the direction of what is not being selected by the third query.

muniappank
Starting Member

7 Posts

Posted - 2010-05-17 : 17:03:33

Thank you very much for all your replies
It is working, If I am using CONVERT(decimal,Quantity) instead off Quantity. It is the issue related to colummn datatype.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources