Getting only Month and Year from SQL DATE
SqlSql ServerTsqlSql Problem Overview
I need to access only Month.Year from Date field in SQL Server.
Sql Solutions
Solution 1 - Sql
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,
), 0) AS [year_month_date_field] FROM
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
Solution 2 - Sql
select month(dateField), year(dateField)
Solution 3 - Sql
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/
Solution 4 - Sql
SELECT DATEPART(yy, DateVal)
SELECT DATEPART(MM, DateVal)
SELECT DATENAME(MM, DateVal)
Solution 5 - Sql
There are two SQL function to do it:
- DATEPART()
- YEAR() and MONTH().
Refer to the linked documentation for details.
Solution 6 - Sql
datename(m,column)+' '+cast(datepart(yyyy,column) as varchar) as MonthYear
the output will look like: 'December 2013'
Solution 7 - Sql
Some of the databases such as MS ACCESS
or RODBC
may not support the SQL SERVER
functions, but for any database that has the FORMAT
function you can simply do this:
SELECT FORMAT(<your-date-field>,"YYYY-MM") AS year-date FROM <your-table>
Solution 8 - Sql
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);
Solution 9 - Sql
let's write it this way: YEAR(anySqlDate)
and MONTH(anySqlDate)
. Try it with YEAR(GETDATE())
for example.
Solution 10 - Sql
convert(varchar(7), <date_field>, 120)
because 120 results in 'yyyy-MM-dd' which is varchar(10)
using varchar(7) will display only year and month
example:
select convert(varchar(7), <date_field>, 120), COUNT(*)
from <some_table>
group by convert(varchar(7), <date_field>, 120)
order by 1
Solution 11 - Sql
I am interpreting your question in two ways.
a) You only need Month & Year seperately in which case here is the answer
select
[YEAR] = YEAR(getdate())
,[YEAR] = DATEPART(YY,getdate())
, [MONTH] = month(getdate())
,[MONTH] = DATEPART(mm,getdate())
,[MONTH NAME] = DATENAME(mm, getdate())
b)
You want to display from a given date say '2009-11-24 09:01:55.483'
in MONTH.YEAR format. So the output should come as 11.2009
in this case.
If that is supposed to be the case then try this(among other alternatives)
select [Month.Year] = STUFF(CONVERT(varchar(10), GETDATE(),104),1,3,'')
Solution 12 - Sql
RIGHT(CONVERT(VARCHAR(10), reg_dte, 105), 7)
Solution 13 - Sql
Try this
select to_char(DATEFIELD,'MON') from YOUR_TABLE
eg.
select to_char(sysdate, 'MON') from dual
Solution 14 - Sql
CONCAT (datepart (yy,DATE), FORMAT (DATE,'MM'))
gives you eg 201601 if you want a six digit result
Solution 15 - Sql
Try this:
Portuguese
SELECT format(dateadd(month, 0, getdate()), 'MMMM', 'pt-pt') + ' ' + convert(varchar(10),year(getdate()),100)
Result: maio 2019
English
SELECT format(dateadd(month, 0, getdate()), 'MMMM', 'en-US') + ' ' + convert(varchar(10),year(getdate()),100)
Result: May 2019
If you want in another language, change 'pt-pt' or 'en-US' to any of these in link
Solution 16 - Sql
select CONCAT(MONTH(GETDATE()),'.',YEAR(GETDATE()))
Output: **5.2020**
select CONCAT(DATENAME(MONTH , GETDATE()),'.',YEAR(GETDATE()))
Output: **May.2020**
Solution 17 - Sql
For reporting purposes I tend to use
CONCAT(YEAR([DateColumn]), RIGHT(CONCAT('00', MONTH([DateColumn])), 2))
You will lose the date type, since that will return (n)varchar. But with the leading zero this column is sortable (e.g. '202112' > '202102'). You could also add a seperator between year and month like
CONCAT(YEAR([DateColumn]), '-', RIGHT(CONCAT('00', MONTH([DateColumn])), 2))
returning somethink like '2020-08' (still sortable).
Solution 18 - Sql
For MySQL, this works pretty well:
DATE_FORMAT(<your-date>, '%Y-%m %M')
It goes into the SELECT
statement. Here's the output:
2014-03 March
For more information about DATE_FORMAT
, check out the documentation:
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format
Solution 19 - Sql
I had a specific requirement to do something similar where it would show month-year which can be done by the following:
SELECT DATENAME(month, GETDATE()) + '-' + CAST(YEAR(GETDATE()) AS nvarchar) AS 'Month-Year'
In my particular case, I needed to have it down to the 3 letter month abreviation with a 2 digit year, looking something like this:
SELECT LEFT(DATENAME(month, GETDATE()), 3) + '-' + CAST(RIGHT(YEAR(GETDATE()),2) AS nvarchar(2)) AS 'Month-Year'
Solution 20 - Sql
Try SELECT CONCAT(month(datefield), '.', year(datefield)) FROM YOURTABLE;
Solution 21 - Sql
Another simple answer is to remove the number of day from the date
Here how to do it in BigQuery
SELECT DATE_ADD(<date_field>, INTERVAL 1 - extract(day from <date_field>) DAY) as year_month_date
An exemple :
SELECT DATE_ADD(date '2021-03-25', INTERVAL 1 - extract(day from date '2021-03-25') DAY) as year_month_date
This return 2021-03-01
Solution 22 - Sql
My database doesn't support most of the functions above however I found that this works:
SELECT * FROM table WHERE SUBSTR(datetime_column, starting_position, number_of_strings)=required_year_and_month;
for example:
SELECT SUBSTR(created, 1,7) FROM table;
returns the year and month in the format "yyyy-mm"
Solution 23 - Sql
select convert(varchar(11), transfer_date, 106)
got me my desired result of date formatted as 07 Mar 2018
My column 'transfer_date' is a datetime type column and I am using SQL Server 2017 on azure
Solution 24 - Sql
For result: "YYYY-MM"
SELECT cast(YEAR(<DateColumn>) as varchar) + '-' + cast(Month(<DateColumn>) as varchar)
Solution 25 - Sql
Get Month & Year From Date
DECLARE @lcMonth nvarchar(10)
DECLARE @lcYear nvarchar(10)
SET @lcYear=(SELECT DATEPART(YEAR,@Date))
SET @lcMonth=(SELECT DATEPART(MONTH,@Date))
Solution 26 - Sql
Query :- Select datename(m,GETDATE())+'-'+cast(datepart(yyyy,GETDATE()) as varchar) as FieldName
Output :- January-2019
general datefield we can use
datename(m,<DateField>)+' '+cast(datepart(yyyy,<DateField>) as varchar) as FieldName
Solution 27 - Sql
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-')
Output: Mar-2019
Solution 28 - Sql
SELECT * FROM demo
WHERE attendance_year_month < SUBSTRING(CURRENT_TIMESTAMP,1,11)
AND
attendance_year_month >= DATE_SUB(SUBSTRING(CURRENT_TIMESTAMP,1,11), INTERVAL 6 MONTH)