locked
DATE CALC: Operand type clash: date is incompatible with int RRS feed

  • Question

  • Okay, so I'm stumped.  Why does my code keep erroring out?  What am I missing here in my WHERE clause?
    My desired results is to return only the last record of the table. 

    -----------------------------

    IF OBJECT_ID(N'tempdb..#TIME_STATS') IS NOT NULL BEGIN DROP TABLE #TIME_STATS END
     
     CREATE TABLE #TIME_STATS
    ([CREATED_DATE] DATETIME,
     [WK_DAY] DATE,
     [NAME] VARCHAR(100),
     )

    INSERT INTO #TIME_STATS (CREATED_DATE,WK_DAY,NAME)
    VALUES
    ('2020-08-08 16:08:28.000','2020-08-11','SMITH'),
    ('2020-08-09 12:08:40.000','2020-08-11','JONES'),
    ('2020-08-10 16:06:16.000','2020-08-11','MILLER')

    SELECT *
    FROM #TIME_STATS
    WHERE CAST(CREATED_DATE AS DATE) = cast(WK_DAY-1 as date)

    Thursday, August 13, 2020 6:19 PM

Answers

  • SELECT *
    FROM #TIME_STATS
    WHERE CAST(CREATED_DATE AS DATE) = DATEADD(DAY, -1, WK_DAY)

    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by BrianJohn Thursday, August 13, 2020 6:34 PM
    Thursday, August 13, 2020 6:29 PM

All replies

  • SELECT *
    FROM #TIME_STATS
    WHERE CAST(CREATED_DATE AS DATE) = DATEADD(DAY, -1, WK_DAY)

    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by BrianJohn Thursday, August 13, 2020 6:34 PM
    Thursday, August 13, 2020 6:29 PM
  • Guoxiong, Thank you for the answer. Brian
    Thursday, August 13, 2020 6:35 PM
  • BTW, if you're curious about explanation as to why, I remember this blog post telling you why (from 2008 - I just dated myself :)

    https://blogs.lessthandot.com/index.php/datamgmt/datadesign/operand-type-clash-date-is-incompatible-2008/


    Looking for new opportunities

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


    My blog


    My TechNet articles

    Thursday, August 13, 2020 6:37 PM
  • BTW, if you're curious about explanation as to why, I remember this blog post telling you why (from 2008 - I just dated myself :)

    Well, it is quite obvious why. A date is a date, and an integer is an integer, just like an apple is an apple, and an orange is an orange.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, August 13, 2020 9:38 PM
  • You can get away with adding numbers to DateTime variable or using 0 for default date, etc. (I never do this myself and don't like seeing in someone's else code either)

    Looking for new opportunities

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


    My blog


    My TechNet articles

    Thursday, August 13, 2020 9:48 PM
  • You can get away with adding numbers to DateTime variable or using 0 for default date, etc. (I never do this myself and don't like seeing in someone's else code either)

    Yes, but just because you can, does not mean that you should. After all, if @dt is datetime, what does @dt + 1 mean? Is that a second, a day, a year or what?

    They did away with these silly things when they introduced the new date and time data types.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Naomi N Thursday, August 13, 2020 11:05 PM
    Thursday, August 13, 2020 10:01 PM