Getting only Month and Year from SQL DATE
I need to access only Month.Year from Date field in SQL Server.
As well as the suggestions given already, there is one other possiblity I can infer from your question:
- You still want the result to be a date
- But you want to 'discard' the Days, Hours, etc
- Leaving a year/month only date field
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, <dateField>), 0) AS [year_month_date_field] FROM <your_table>
This gets the number of whole months from a base date (0) and then adds them to that base date. Thus rounding Down to the month in which the date is in.
NOTE: In SQL Server 2008, You will still have the TIME attached as 00:00:00.000 This is not exactly the same as "removing" any notation of day and time altogether. Also the DAY set to the first. e.g. 2009-10-01 00:00:00.000
select month(dateField), year(dateField)
Read more… Read less…
SELECT convert(varchar(7), getdate(), 126)
You might wanna check out this website: http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/
SELECT DATEPART(yy, DateVal) SELECT DATEPART(MM, DateVal) SELECT DATENAME(MM, DateVal)
datename(m,column)+' '+cast(datepart(yyyy,column) as varchar) as MonthYear
the output will look like: 'December 2013'
This can be helpful as well.
SELECT YEAR(0), MONTH(0), DAY(0);
SELECT YEAR(getdate()), MONTH(getdate()), DAY(getdate());
SELECT YEAR(yourDateField), MONTH(yourDateField), DAY(yourDateField);