0

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

Taryn
  • 242,637
  • 56
  • 362
  • 405
Andy
  • 373
  • 1
  • 7
  • 24
  • 1
    I'm not 100% sure I understand what you are trying to achive,, but why don't use computed columns (from the syntax I assume SQLServer)? – a1ex07 Sep 08 '13 at 22:27
  • Yup, SQL Server. I'm not using computer columns because I have no idea what that is. I want to get the year of "date_hired" and store it in a variable for use later. The only way I know is to create a new column and then go like that. – Andy Sep 08 '13 at 22:30
  • Syntax is very similar to regular column, http://technet.microsoft.com/en-us/library/ms188300.aspx , in your case it would be something like `ALTER TABLE ... ADD [DAY] as DATEPART(DAY, date_hired) ` – a1ex07 Sep 08 '13 at 22:44
  • I don't want to add a table though? – Andy Sep 08 '13 at 22:53
  • YOU CAN USE SUBSTRING THEN CAPTURE ONLY CHARACTERS YOU LIKE – zxc Sep 09 '13 at 03:26

2 Answers2

0

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.

Praveen Lobo
  • 6,956
  • 2
  • 28
  • 40
0

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

DECLARE @YearHired int
SET @Year = (SELECT YEAR(date_hired) FROM hire)
Monty Wild
  • 3,981
  • 1
  • 21
  • 36