SQL - Select only 'Year' from DateTime field

Question

My DB is an SQL Server 2012. I try to select only the year from my datetime filed. The date format is dd/mm/yyyy 00:00:00. Here is my queries:

SELECT 
       ,YEAR(DateTime)
FROM MayTable

But when I run my queries I get this error:

Conversion failed when converting date and/or time from character string.

Solution

You should store this information in a column of appropriate datatype in the first place.

But if I understand your comment correctly this is not a table of your own design. You can use

SELECT CAST(SUBSTRING(DateTime, 7, 4) AS INT)
FROM   MyTable; 

Or

SET DATEFORMAT DMY;

SELECT YEAR(DateTime)
FROM   MyTable; 

Comments

[GolezTrol]

Your datetime field apparently is not a datetime field but a character field. That means to things. First of all, you shouldn't store date times as text, but rather in the specific date and time types available for this purpose. Secondly, as long as it is a text field, you either have to convert it to a date properly, or just use SUBSTR to get the year from it.

[kekimian]

I store nothing in my DB, the DB is from SCVMM...

[kekimian]

@ GolezTrol, can you please give an example?

[Martin Smith]

You would use MONTH(DateTime) in that case. Or DATENAME(MONTH,DateTime)

[kekimian]

Ok, I see I just need to change Year to Month. Thank you very much for your help

[kekimian]

Just to be sure, 'Set' do not change anything in my DB?

[Martin Smith]

No, it just affects how string dates are interpreted in your current session.