I need to access only Month.Year from Date field in SQL Server.
This question is related to
sql
sql-server
tsql
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
let's write it this way: YEAR(anySqlDate)
and MONTH(anySqlDate)
. Try it with YEAR(GETDATE())
for example.
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/
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"
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))
There are two SQL function to do it:
Refer to the linked documentation for details.
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-')
Output: Mar-2019
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);
Try SELECT CONCAT(month(datefield), '.', year(datefield)) FROM YOURTABLE;
select CONCAT(MONTH(GETDATE()),'.',YEAR(GETDATE()))
Output: 5.2020
select CONCAT(DATENAME(MONTH , GETDATE()),'.',YEAR(GETDATE()))
Output: May.2020
SELECT DATEPART(yy, DateVal)
SELECT DATEPART(MM, DateVal)
SELECT DATENAME(MM, DateVal)
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>
Try this
select to_char(DATEFIELD,'MON') from YOUR_TABLE
eg.
select to_char(sysdate, 'MON') from dual
For result: "YYYY-MM"
SELECT cast(YEAR(<DateColumn>) as varchar) + '-' + cast(Month(<DateColumn>) as varchar)
CONCAT (datepart (yy,DATE), FORMAT (DATE,'MM'))
gives you eg 201601 if you want a six digit result
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
select month(dateField), year(dateField)
datename(m,column)+' '+cast(datepart(yyyy,column) as varchar) as MonthYear
the output will look like: 'December 2013'
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
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
RIGHT(CONVERT(VARCHAR(10), reg_dte, 105), 7)
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'
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,'')
Source: Stackoverflow.com