issue with date - 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 2000 Forums

Transact-SQL (2000)

issue with date

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-11-18 : 17:03:06

SELECT @total_count=count(*)
FROM tbl_pay
WHERE pay_id = @pay_id
AND
CONVERT(varchar(8),date_of_payment,1)> CONVERT(varchar(8),getdate(),1)

Here when I run this query it gives only 1 as the count

and

SELECT @total_count=count(*)
FROM tbl_pay
WHERE pay_id = @pay_id
AND
date_of_payment> getdate()

will give me 2 which is right. the date_of_payment is date field and can I compare directly without converting to varchar

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-11-18 : 18:53:04

Your first query will not consider time.
your Second Query will consider current time as well.

quote:

Which is right?

That depends on what you need?
cheers

mk_garg

gpl
Posting Yak Master

195 Posts

Posted - 2004-11-18 : 18:55:19

yes of course you can

what you are actually comparing is 2 strings - the date converted to month/day/year format, eg
'07/11/03' > '06/11/04'

but as a date, the opposite is true
It is also very inefficient to do this

Graham

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-11-18 : 19:14:28

thanks a lot........

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources