Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2012
    Posts
    63

    Unanswered: where date > this year

    Hello all again,

    I have an SQL string which ends like so:

    WHERE [End_Date] > Year(now())

    however... this doesnt work. I think its because "Year" is a function that is used on columns but... how can I obtain the current year to use in this manner?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    GetDate().

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2012
    Posts
    63
    Quote Originally Posted by Pat Phelan View Post
    GetDate().

    -PatP
    I assume you mean, replace now() with getdate() however that also doesnt work =(
    "invalid column name 'GetDate'"

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You need the open/close parentheses to indicate it is a function.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Still won't work, since year() returns an integer value, not a date. If your [End_Date] column is a date or datetime type, you will need:

    WHERE year([End_Date]) > Year(now())

    ...or this, which is probably better:

    WHERE [End_Date] > dateadd(year, datediff(year, 0, getdate()), 0)

    That may look klunky, but it is faster and likely closer to your actual requirements.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Dec 2012
    Posts
    63
    Quote Originally Posted by blindman View Post
    WHERE [End_Date] > dateadd(year, datediff(year, 0, getdate()), 0)
    could you explain how this works...

    dateadd adds a value to a specific part of the date given so we have...
    add some number of years to 0.
    so the end date will equal... the same...

    datediff calculates the difference between two dates so...
    difference in date between 0 and todays date (this gives an integer value)

    so as far as I can see... dateadd does nothing but turn this into a date value but how is it able to do that from just a year value? essentially you havent given a single date to dateadd and yet it returns a date.

    furthermore... is there not a better way of turning that integer value into a date? I was thinking like... CAST or something similar that could do this?

  7. #7
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    What SQL product used NOW()? Why would you use GETDATE()? That was Sybase and UNIX decades ago. We have CURRENT_TIMESTAMP in T-SQL now! Avoid needless proprietary code.

    Why not hard code it? Just use “something_end_date > '2014-12-31';” and be done with it. Or are you trying to do something else?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The Microsoft Jet database engine (used by Microsoft Access and VB) used the Now() syntax. You are correct, CURRENT_TIMESTAMP is a better choice than GetDate(), and a constant is probably better still.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by penfold1992 View Post
    could you explain how this works...
    Try it: [code]select dateadd(year, datediff(year, 0, getdate()), 0)[/QUOTE]
    The inner datediff function counts the number of years since year zero.
    The outer dateadd function adds that number of years to year zero.
    The result is a datetime value that is a whole year.
    Because these are arithmetic operations rather than string or conversions, the operations is very fast.

    Quote Originally Posted by penfold1992 View Post
    essentially you havent given a single date to dateadd and yet it returns a date.
    Dates are stored as numbers in sql server, with zero representing Jan 1st, 1900:

    Code:
    declare @DateTime datetime = 0
    select @DateTime
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •