SQL DATEPART without creating a new column

Question

I wish to be able to be able to do something like this for use with @YearVar later.

SET @YearVar DATETIME = CONVERT(nvarchar, date_hired, 106),
DATEPART(YEAR, date_hired)

However the only way I know how to datepart is like this :

SELECT CONVERT(nvarchar, date_hired, 106) As Hire_Date,
DATEPART(DAY, date_hired) As [Day],
DATEPART(MONTH, date_hired) As [Month],
DATEPART(YEAR, date_hired) AS [Year]
FROM hire

Any help would be great thanks.

Andy

Answer 1

You can declare and set the variables like shown below.

DECLARE @YearVar int = (select DATEPART(YEAR, GETDATE()))    

OR

DECLARE  @YearVar int    
SET @YearVar =  (select DATEPART(YEAR, GETDATE()))    

The select query should return only one value.

if you want to set the value once and use it in a query as you have shown, it will always give you the same value. If you are looking to calculate year for every row in the table, the query you have shown will work. Alternatively, you can create a computed column as ALTER TABLE hire ADD year as DATEPART(YEAR, date_hired) but that will execute DATEPART for every row in the table.

Answer 2

From your question and comments, you would be looking for something like:

DECLARE @YearHired int
SET @Year = (SELECT YEAR(date_hired) FROM hire)