Geek Answers Handbook

Storing an entire month and year in SQL Server 2008?

Is it possible to save month / year in SQL Server 2008 with regular datetime? I don't need a timestamp, and the column will store unique mo / yr combinations (i.e. 10-11, 11-11, 12-11, etc.).

Any recommendations?

+8
sql sql-server-2008
source share
4 answers

Without knowing the intended use, it is impossible to give the right recommendations. However, storing the month and year is easily done in at least three ways:

  • use the date field, but always store the first day of the month at midnight in it; then always adjust the format of the date field to display
  • add integer fields of the year and month and fill them out, dividing the date
  • add an integer field in which you encode it as a year * 100 + month or some other useful scheme.
+14
source share

The Sql server will store the entire date and time data (year-month-hour of the day: min: sec.milliSec) no matter what.

When you save your dates, you can make the day the 1st of the month. Just format the dates as you want when you fulfill your requests.

http://www.sql-server-helper.com/tips/date-formats.aspx

+2
source share

You cannot store only the year and month in the DateTime column. Well, what you can do is default the rest of the values. i.e.: 2011.10.1.1, 2011.11.1.1 like this.

Or you can save it as a string.

+1
source share

From SQLServer 2008 onwards:

Save as the Date column and add the following check constraint to make sure the value is always the first month:

datepart (month, MyDate) <> datepart (month, dateadd (day, -1, MyDate))

+1
source share

More articles:


All Articles