simple date compare problem - 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)

simple date compare problem

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2013-05-10 : 07:29:59

Hi all,

I want to a select:

SELECT @counter=COUNT(shipdate) FROM T_Order_Main
WHERE OrderID = @Ordernr AND Shipdate = '01-01-1900 00:00:00'
Shipdate (datetime) in the DB has this value '01-01-1900 00:00:00'
but when i try the above statement it

any idea how to solve this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-10 : 07:45:52

is shipdate of type datetime?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-10 : 09:25:36

quote:
Originally posted by mike13

Hi all,

I want to a select:

SELECT @counter=COUNT(shipdate) FROM T_Order_Main
WHERE OrderID = @Ordernr AND Shipdate = '01-01-1900 00:00:00'
Shipdate (datetime) in the DB has this value '01-01-1900 00:00:00'
but when i try the above statement it

any idea how to solve this?

Don't know if it is my firewall causing the problem, but your posting reads as "but when i try the above statement it". What happens when you try the statement? Error message? No rows returned?

If you don't see any rows in the output, add another statement to select the value of @counter:

SELECT @counter=COUNT(shipdate) FROM T_Order_Main 
WHERE OrderID = @Ordernr AND Shipdate = '01-01-1900 00:00:00'

SELECT @counter;

mike13
Posting Yak Master

219 Posts

Posted - 2013-05-10 : 09:35:41

value of the counter is always 0 even if the it exists.

yes it is datetime

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-10 : 10:02:47

See if this returns any rows:

SELECT * FROM T_Order_Main 
WHERE OrderID = @Ordernr AND Shipdate = '19000101'

If it does not return any rows, run the following query, use the OrderId you get from that to set the value of @ordernr and then try again

SELECT TOP 1 OrderID FROM T_Order_Main  WHERE Shipdate = '19000101'

mike13
Posting Yak Master

219 Posts

Posted - 2013-05-11 : 13:09:47

Thanks a lot.

Fault was mine. seems i had code (vb.net) that did some changes to the table before this SP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources