I’m currently working on a query that returns records from within a date range. The ideal is for the start date to be everything from 2015 onward. In theory, shouldn’t each of the three SET statements below set the variable to 2015*?

DECLARE @startDate datetime;
SET @startDate = '20150101';
SET @startDate = YEAR(GETDATE());
SET @startDate = DATEPART(yyyy,GETDATE());

Only the first one, the hardcoded date, behaves as expected. The other two return ALL records that are being queried through. Am I missing something here?

  • Know when to retry or fail when calling SQL Server from C#?
  • SSIS Package failing because “script task is failing because the script is not precompiled”
  • Update column based on combination of other columns
  • Creating a stored procedure with many parameters from the website
  • What is the difference between TEMPORARY TABLE and TABLE VARIABLE in SQL 2008?
  • What is a good way to generate a set of (relatively) short “invitation codes”
  • *EDIT: I apologize for how unclear I was with that initially. Basically, @startDate should be set to 01-01-XXXX, where XXXX is whatever year today’s date is a part of. It’s being compared against another DATETIME variable. I hope that clarifies things.

    3 Solutions collect form web for “SQL YEAR(GETDATE())”

    The answer to your question is “No”. The variable @StartDate is date time. This doesn’t make sense:

    set @startDate = 2015

    It doesn’t make sense. An integer that looks like a year is not a date.

    If you want the first day of the year, you can do:

    set @startDate = dateadd(day,
                             1 - datepart(dayofyear, getdate()),
                             cast(getdate() as date)
                             ) as FirstDayOfYear

    I think this would work (for SQL-Server):

    SET @startDate = cast(YEAR(GETDATE()) as varchar(4))
    SET @startDate = cast(DATEPART(yyyy,GETDATE()) as varchar(4))

    This will show you what’s happening:

    DECLARE @startDate datetime
    SET @startDate = '20150101'
    select @startdate
    SET @startDate = YEAR(GETDATE()) 
    select @startdate
    SET @startDate = cast(YEAR(GETDATE()) as varchar(4))
    select @startdate
    SET @startDate = DATEPART(yyyy,GETDATE()) 
    select @startdate
    SET @startDate = cast(DATEPART(yyyy,GETDATE()) as varchar(4))
    select @startdate

    YEAR(GETDATE()) and DATEPART(yyyy,GETDATE()); will return just the year part of the date, so if you ran them today you would get 2015 back, not the date 2015-01-01 as you seem to want.

    If you want to force the date value to the beginning of the current year, one way would be:

    SET @startDate = YEAR(GETDATE()) + '-01-01';
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.