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)
 datatype error

Author  Topic 

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2009-04-21 : 10:03:57
Hello,

Please help me to correct below statement. I got error message saying that "Invalid Data type Comparision". And all the dates are format as 'Date' as data type. Thank you for your help...

where(c.payment is null or c.payment_date between a.item_date_action and
case when b.closeddate is null then '2009-03-31'
when b.closeddate >'2009-03-31' then '2009-03-31'
else b.closeddate end)

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-21 : 10:25:59
Try '03/31/2009' instead of '2009-03-31'
Go to Top of Page

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2009-04-21 : 10:36:27
quote:
Originally posted by whitefang

Try '03/31/2009' instead of '2009-03-31'



still does not work...that could not convert to datestamp as an error.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-21 : 10:47:13
What are the datatypes of the columns payment_date and item_date_action?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2009-04-21 : 10:54:34
Both are format as "Date" as datatype. Thanks,


quote:
Originally posted by madhivanan

What are the datatypes of the columns payment_date and item_date_action?

Madhivanan

Failing to plan is Planning to fail

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-21 : 10:56:48
quote:
Originally posted by nt4vn

Both are format as "Date" as datatype. Thanks,


quote:
Originally posted by madhivanan

What are the datatypes of the columns payment_date and item_date_action?

Madhivanan

Failing to plan is Planning to fail




There is no such DATE datatype in SQL Server 2005?
Did you mean DATETIME datatype?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2009-04-21 : 11:00:36
Yes, but I used Cast function ...and it work...don't know why...

cast('2009-03-31' as date)

Thanks All!

quote:
Originally posted by madhivanan

quote:
Originally posted by nt4vn

Both are format as "Date" as datatype. Thanks,


quote:
Originally posted by madhivanan

What are the datatypes of the columns payment_date and item_date_action?

Madhivanan

Failing to plan is Planning to fail




There is no such DATE datatype in SQL Server 2005?
Did you mean DATETIME datatype?

Madhivanan

Failing to plan is Planning to fail

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-22 : 01:51:29


See what happens when you run this

where(c.payment is null or c.payment_date between a.item_date_action and
case when b.closeddate is null then '20090331'
when b.closeddate >'20090331' then '20090331'
else b.closeddate end)

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -