Get Year and Month from SQL

Getting only Month and Year from SQL DATE

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

How to return only the year and month from a SQL Server DateTime datatype

SELECT FORMAT ( GETDATE() , 'yyyy-MM' )

Ok so if you have a Table named MyTable with a Column named DateCol of Type DateTime you can use the query below:

SELECT FORMAT ( DateCol , 'yyyy-MM' ) FROM MyTable 

How to get month and year from date in SQL

This may help:

SQL Server:

SELECT FORMAT (GETDATE(), 'MMM yyyy')  -- Jul 2019
SELECT FORMAT (GETDATE(), 'MMMM yyyy') -- July 2019
SELECT RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8) -- Jul 2019

For more details: https://www.tutorialgateway.org/sql-date-format/

MySQL:

SELECT DATE_FORMAT("20150102", "%M %Y");  -- January 2015
SELECT DATE_FORMAT("20150102", "%b %Y"); -- Jan 2015
SELECT YEAR(date) AS 'year', MONTH(date) AS 'month'

For more details: http://www.sqlines.com/mysql-to-oracle/date_format

Get only month and year in SQL Server

As an alternative approach, you could go for:

RIGHT(REPLACE(CONVERT(varchar(8),DateColumn,3),'/',''),4)

extract year and month from date field mysql

SELECT CONCAT(year(task_completion), '-' ,month(task_completion)) FROM task

Extracting year and month from datekey in my sql

You can try the below -

select month(convert(datekey,char)) as mon, 
       year(convert(datekey,char)) as yr
from tablename

SQL Current month/ year question

In SQL Server you can use YEAR, MONTH and DAY instead of DATEPART.

(at least in SQL Server 2005/2008, I'm not sure about SQL Server 2000 and older)

I prefer using these "short forms" because to me, YEAR(getdate()) is shorter to type and better to read than DATEPART(yyyy, getdate()).

So you could also query your table like this:

select *
from your_table
where month_column = MONTH(getdate())
and year_column = YEAR(getdate())

Extract month name and year from timestamp to same column

Example for you:

select 
    TO_CHAR(created_at, 'Month') AS "Month", 
    TO_CHAR(created_at, 'YYYY') AS "Year", 
    trim(TO_CHAR(created_at, 'Month')) || ', ' || trim(TO_CHAR(created_at, 'yyyy')) as mydate   
from acc_facts

Result data:

January     2022    January, 2022
January     2022    January, 2022
May         2022    May, 2022
March       2022    March, 2022

Get the previous month number including year

Try with case when like below:

UPDATE Company_Coupon
SET Total_Coupons = @count
WHERE CompanyID = 1205
AND Month = (case when MONTH(GETDATE())-1=0 then 12 else MONTH(GETDATE())-1 end)  AND Year  = (case when MONTH(GETDATE())-1=0 then YEAR (GETDATE())-1 else YEAR (GETDATE()) end)

---

Related Topics