Advertisement
Advertisement


Getting only Month and Year from SQL DATE


Question

I need to access only Month.Year from Date field in SQL Server.

2018/01/23
1
136
1/23/2018 10:22:11 AM

Accepted Answer

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

2012/05/02
173
5/2/2012 6:23:16 PM


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/

2018/03/15

SELECT DATEPART(yy, DateVal)
SELECT DATEPART(MM, DateVal)
SELECT DATENAME(MM, DateVal)
2009/11/23

datename(m,column)+' '+cast(datepart(yyyy,column) as varchar) as MonthYear

the output will look like: 'December 2013'

2014/06/11

There are two SQL function to do it:

Refer to the linked documentation for details.

2017/12/15

This can be helpful as well.

SELECT YEAR(0), MONTH(0), DAY(0);

or

SELECT YEAR(getdate()), MONTH(getdate()), DAY(getdate());

or

SELECT YEAR(yourDateField), MONTH(yourDateField), DAY(yourDateField);
2015/07/29

Source: https://stackoverflow.com/questions/1781946
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]