+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server)
+---- Thread: Getting only Month and Year from SQL DATE (/Thread-Getting-only-Month-and-Year-from-SQL-DATE)
Getting only Month and Year from SQL DATE - jaquithegyz - 07-31-2023
I need to access only Month.Year from Date field in SQL Server.
RE: Getting only Month and Year from SQL DATE - histamines169603 - 07-31-2023
select month(dateField), year(dateField)
RE: Getting only Month and Year from SQL DATE - gantt212 - 07-31-2023
SELECT DATEPART(yy, DateVal)
SELECT DATEPART(MM, DateVal)
SELECT DATENAME(MM, DateVal)
RE: Getting only Month and Year from SQL DATE - thuthuyqjiubhpu - 07-31-2023
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,'')
RE: Getting only Month and Year from SQL DATE - lignea - 07-31-2023
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
RE: Getting only Month and Year from SQL DATE - howlend680 - 07-31-2023
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
RE: Getting only Month and Year from SQL DATE - overwrought988 - 07-31-2023
datename(m,column)+' '+cast(datepart(yyyy,column) as varchar) as MonthYear
the output will look like: 'December 2013'
RE: Getting only Month and Year from SQL DATE - joellenyeczhbw - 07-31-2023
RIGHT(CONVERT(VARCHAR(10), reg_dte, 105), 7)
RE: Getting only Month and Year from SQL DATE - trophoplasmic715216 - 07-31-2023
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);
RE: Getting only Month and Year from SQL DATE - kristienihtcyopn - 07-31-2023
Try this
select to_char(DATEFIELD,'MON') from YOUR_TABLE
eg.
select to_char(sysdate, 'MON') from dual