CODEWITHSUNDEEP
sqlsql-servert-sqlvariables
Eiketsu
Eiketsu

Reputation: 203

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.

Upvotes: 0

Views: 57134

Answers (4)

Chatur
Chatur

Reputation: 1

Try using the below

declare @startdate date;
select @startdate = CONCAT( YEAR(GETDATE()) , '-01-01' );

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269429

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

Upvotes: 3

D Stanley
D Stanley

Reputation: 152491

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';

Upvotes: 0

BWS
BWS

Reputation: 3836

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

Upvotes: 2

Related Questions