Storing just Month and Year in SQL Server 2008?
Without knowing the intended use, it is not possible to advise correctly. However, storing month and year is easily done in at least three ways:
- use a date field, but always store into it the first day of the month at midnight; then always custom format the date field for display
- add integer year and month fields, and populate them by splitting a date
- add an integer field where you encode it as year * 100 + month or some other useful scheme
Sql Server will store the datetime data in its entirety (year-month-day hour:min:sec.milliSec) no matter what.
When you store your dates, you can make the day the 1st of the month. Just format the dates how you want when you do your queries.
From SQLServer 2008 and onwards:
Save as a Date column and add the following check constraint to make sure the value is always the first of the month:
datepart(month, MyDate)<>datepart(month,dateadd(day, -1, MyDate))