Post reply

Error when subtracting two columns

  • April 12, 2007 at 10:00 am

    #175853

    Hi

       I am doing the following and getting an "Invalid operator for data type. Operator equals subtract, type equals varchar" error

    SELECT ID, Name, [Date Received],

    [FirstOfAnnual Date] AS [Annual Date],

    [FirstOfAnnual Date]- [Date Received] AS DIFF

    FROM TEMP WHERE ((([FirstOfAnnual Date]-[Date Received])=0))

    Please help.

    Thanks

    San

     

  • April 12, 2007 at 10:18 am

    #700149

    I'm assuming you're using DATETIME datatypes for those two columns?

    If so, use the DATEDIFF function instead of the subtraction operator. It's a little cleaner and you're less likely to run into run into unexpected results in my experience.

  • April 12, 2007 at 10:24 am

    #700151

    Can you give me an example to the scenario. Thanks.

  • April 12, 2007 at 11:01 am

    #700159

    SELECTID, Name, [Date Received], [FirstOfAnnual Date] AS [Annual Date],

    DATEDIFF(dd, [FirstOfAnnual Date], [Date Received]) AS DIFF

    FROM TEMP WHERE DATEDIFF(dd, [FirstOfAnnual Date], [Date Received]) = 0

    That would limit it to where the two columns fell on the same day (by using the "dd" specifiction with DATEDIFF). However you can use other's, look in books online for a description of them.

  • April 12, 2007 at 11:30 am

    #700177

    Thanks Aaron. But when I used the datediff and ran the query it fecthed "n" number of rows and I compared the same with Access query and there is a difference of 300 odd records.

    Same query with "-" in Access fetched more records than using Datediff with T-SQL.

    So little skeptical. Basically I am migrating from Access to Sql Server. Any ideas.

  • April 12, 2007 at 11:50 am

    #700182

    Since the DATEDIFF was done with 'dd', it's assuming differences in day only. If you're also trying to identify differences in time, which I suspect the Access app deals with in the simple subtraction. I'll bet that explains the difference

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 12, 2007 at 12:03 pm

    #700190

    Thanks so much.

  • April 13, 2007 at 1:17 pm

    #700497

    How about Date1=Date2 instead of Date1-Date2 = 0? Obviously you'll have to deal with the time part of the dates.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply