locked
Case statement with Date Comparison RRS feed

  • Question

  • 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 MoxyPrice

    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.


    What did I do wrong?
    Thanks in advance


    pvong

    Sunday, March 11, 2012 1:31 AM

Answers

  • Try

    select case when datediff(day, CURRENT_TIMESTAMP, PriceDate) = 0 Then Price ELSE 0 end


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Sunday, March 11, 2012 2:14 PM
    • Marked as answer by pvong123 Monday, March 12, 2012 2:21 PM
    Sunday, March 11, 2012 5:16 AM

All replies

  • DATE data type was introduced with SQL Server 2008:

    SELECT CONVERT(DATE, CURRENT_TIMESTAMP)
    -- 2012-03-10

    Date & Time functions:
    http://www.sqlusa.com/bestpractices/datetimeconversion/


    Kalman Toth SQL SERVER & BI TRAINING

    • Proposed as answer by Naomi N Sunday, March 11, 2012 5:15 AM
    Sunday, March 11, 2012 1:49 AM
  • Can you tell me how to do it with SQL 2005?

    pvong

    Sunday, March 11, 2012 1:52 AM
  • One way to do what you want is

    SELECT CASE WHEN DATEDIFF(day, pricedate, getdate()) = 0 THEN price ELSE '0' END AS Result
    FROM MoxyPrice

    Tom


    • Edited by Tom Cooper Sunday, March 11, 2012 1:30 PM Corrected syntax
    Sunday, March 11, 2012 2:41 AM
  • Try

    select case when datediff(day, CURRENT_TIMESTAMP, PriceDate) = 0 Then Price ELSE 0 end


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Sunday, March 11, 2012 2:14 PM
    • Marked as answer by pvong123 Monday, March 12, 2012 2:21 PM
    Sunday, March 11, 2012 5:16 AM
  • convert(varchar, pricedate, 106) = convert(varchar, getdate(), 106) 
    Sunday, March 11, 2012 11:21 AM