sqlsql-servert-sqlvariables

SQL YEAR(GETDATE())

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?

*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.

Solution

  • 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