0

I am using SQL Server Management Studio to create a new table. I have a "HistoryYear" column that has a data type of datetime2.

How do I get the year only as the output? Do I change the data type? Or what do I put into the Default Value or Binding section?

Thank you!

BillyMadison
  • 93
  • 2
  • 10
  • Do you have other Date Columns on the table? Can you post the TSQL definition of the table? – db_brad Mar 25 '15 at 19:07
  • I have a CreatedDate column and a ModifiedDate column. Both use the datetime2 data type (But I'm not sure how to get the ModifiedDate to work because it contains Null values). But how do I find the TSQL definition? – BillyMadison Mar 25 '15 at 19:18

1 Answers1

3

If you only care about the year , you should use an int instead of a datetime2.

To have the column default to just the year, you can set the default value to YEAR(GETDATE()). This takes the current date (and time) and extracts the year as an int.

Matthew Jaspers
  • 1,546
  • 1
  • 10
  • 13
  • If I change the data type to int, how will SQL know that I want the year? – BillyMadison Mar 25 '15 at 19:41
  • Is the data you're inputting to the table already a date? If it's not, you just insert the year as a number. If it is, then depending on how you're loading data, there are plenty of ways to get just the year from a date. If you really want to stick with a date column, you can use the [YEAR()](https://msdn.microsoft.com/en-us/library/ms186313.aspx) function to get just the year part of the date when selecting data. – Matthew Jaspers Mar 25 '15 at 19:45
  • For example, my column HistoryYear, I want the year in which the data is inputted. So, I think the easiest way is to just use a datetime2 data type and just use the year. But when I used YEAR() in the default value or binding, with a data type of datetime2, I get an error saying "Error validating the default for column 'HistoryYear'. – BillyMadison Mar 25 '15 at 19:56
  • That isn't how YEAR() works. If you just want the YEAR the data is input, for an int column you can specify a default of YEAR(GETDATE()). For a datetime2 column you would use just GETDATE(), which will be the exact time the record is inserted and not just the year. Trying to put just a year into a datetime field is more complicated and not recommended. – Matthew Jaspers Mar 25 '15 at 19:59
  • int -datatype and year(getdate()) was exactly what I was looking for! Thank you very much! – BillyMadison Mar 25 '15 at 20:05