Case statement with Date Comparison

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)

Case statement with Date Comparison

Author

Topic

pvong
Yak Posting Veteran

58 Posts

Posted - 2012-03-09 : 23:27:29

What am I doing wrong?

Simple table with 2 columns.

PriceDate is a DateTime
Price is a Float

All I want to do is see if JUST THE DATE from PriceDate is today (don't care about the time) and if it is, give me the price, else give me '0'

This is what I had tried.

SELECT CASE WHEN CONVERT(date, pricedate) = CONVERT(date, getdate()) THEN price ELSE '0' END AS Result
FROM Table

What did I do wrong?
Thanks in advance

------------------------------
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-09 : 23:34:47

i don't see anything wrong with that query.

What is the problem you are facing ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 23:39:53

why convert to varchar? since price is float do like

SELECT CASE WHEN CONVERT(date, pricedate) = CONVERT(date, getdate()) THEN price ELSE 0.00 END AS Result
FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pvong
Yak Posting Veteran

58 Posts

Posted - 2012-03-10 : 20:14:32

visakh16 - It's still not working.

This is the error msg I'm getting and I did double check that PriceDate is a DateTime datatype and Price is set as a Float.

Msg 243, Level 16, State 1, Line 1
Type date is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type date is not a defined system type.

Thanks!

------------------------------
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-10 : 20:33:40

date data type is not available for 2005


SELECT CASE WHEN dateadd(day, datediff(day, 0, pricedate), 0) = dateadd(day, datediff(day, 0, getdate()), 0) 
            THEN price ELSE '0' END AS Result
FROM Table

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-10 : 23:10:03

quote:
Originally posted by pvong

visakh16 - It's still not working.

This is the error msg I'm getting and I did double check that PriceDate is a DateTime datatype and Price is set as a Float.

Msg 243, Level 16, State 1, Line 1
Type date is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type date is not a defined system type.

Thanks!

------------------------------
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.

you should be using datetime in SQL 2005
here in this case since what you're trying to do is stripping time part you can use logic Tan gave

Read this also

http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources