CODEWITHSUNDEEP
sqlsql-server-2008
genxgeek
genxgeek

Reputation: 13357

Storing just Month and Year in SQL Server 2008?

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

Any recommendations?

Upvotes: 10

Views: 20646

Answers (5)

Thomsen
Thomsen

Reputation: 773

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))

Upvotes: 2

Cuong Trinh
Cuong Trinh

Reputation: 347

SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]

You should try this

Upvotes: 0

Nick Rolando
Nick Rolando

Reputation: 26167

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.

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

Upvotes: 3

DarthVader
DarthVader

Reputation: 55032

You cant only store year and month in a DateTime column. Well, what you can do is default the rest of the values. ie: 2011.10.1.1 , 2011.11.1.1 like that.

Or you can store it as string.

Upvotes: 1

wallyk
wallyk

Reputation: 57774

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

Upvotes: 18

Related Questions