Post reply

Working with dates

  • August 16, 2007 at 7:10 am

    #178118

    I am trying to set up a where clause that compares the year of a smalldatetime column with the current year, but I am not having any luck getting it to work (it runs but does not return the right data).

    DECLARE @cDate int

    SET @cDate = YEAR(GETDATE()) - 1

    SELECT *

    FROM TransactionData tr

    WHERE DATEPART(yyyy,tr.TransactionDate) = @cDate

    This query is returning data from every year, and not just from last year as I had intended. Do these functions not work the way I think they do?

  • August 16, 2007 at 8:09 am

    #727343

    Works fine for me. Have you double-checked the rest of your syntax?

    Also try replacing the variable with a hard value.

    Regards,

    goodguy

    Experience is a bad teacher whose exams precede its lessons

  • August 16, 2007 at 8:10 am

    #727344

    Are you looking at the right dates? Meaning are there multiple dates in the table and the one in the WHERE clause is the right one?

    It worked for me as well. Here's what I used.

    -- create table TransactionData

    -- ( TransactionDate smalldatetime

    -- , tid int identity(1,1)

    -- )

    -- go

    -- insert TransactionData select '1/1/2005'

    -- insert TransactionData select '2/1/2005'

    -- insert TransactionData select '3/1/2005'

    -- insert TransactionData select '4/1/2005'

    -- insert TransactionData select '5/1/2005'

    -- insert TransactionData select '1/1/2006'

    -- insert TransactionData select '2/1/2006'

    -- insert TransactionData select '3/1/2006'

    -- insert TransactionData select '4/1/2006'

    -- insert TransactionData select '5/1/2006'

    -- insert TransactionData select '1/1/2007'

    -- insert TransactionData select '2/1/2007'

    -- insert TransactionData select '3/1/2007'

    -- insert TransactionData select '4/1/2007'

    -- insert TransactionData select '5/1/2007'

    DECLARE @cDate int

    SET @cDate = YEAR(GETDATE()) - 1

    SELECT *, @cDate

    FROM TransactionData tr

    WHERE DATEPART(yyyy,tr.TransactionDate) = @cDate

    -- drop table TransactionData

  • August 16, 2007 at 8:17 am

    #727350

    Works fine for me also.

  • August 16, 2007 at 8:21 am

    #727354

    The problem must lie with another part of the query because when I isolate the date comparisons they appear to work correctly, but when run with the other criteria the incorrect data gets returned (for example, the comparison does not return 2007 transactions when I search for 2003, but when the entire where clause is run transactions from years other than 2003 are returned.) Could this be a result of my joins? I'm at a loss to explain this, and I have no idea how to fix it.

    Also, is that more wasteful of resources than placing the equation itself (YEAR(GETDATE()) - 1, for example) directly in the query? Is the difference negligible?

  • August 16, 2007 at 9:34 am

    #727402

    Can you post the DDL for the table, the query you are running, and some sample data?

     

  • August 16, 2007 at 11:00 am

    #727436

    I can't really post that much information (it's not mine to post). The query involves inner joins on seven very large tables, and the query is very much as I described it (with a lot of the detail left out). I know that this isn't much help. Sorry. I guess it boils down to whether the joins could impact the query in such a way that one of the conditions in the where clause could be ignored.

  • August 16, 2007 at 12:04 pm

    #727464

    I fixed the problem. I had forgotten to surround a group of OR-qualified statements in parenthesis, which had an odd effect on the rest of the query. Thanks for all the help. It's appreciated.

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

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