Get Month and Year from a Datetime in SQL Server 2005

Get month and year from a datetime in SQL Server 2005

If you mean you want them back as a string, in that format;

SELECT 
  CONVERT(CHAR(4), date_of_birth, 100) + CONVERT(CHAR(4), date_of_birth, 120) 
FROM customers

Here are the other format options

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

Get year and month(as int) from datetime

select stuff(convert(varchar(6), Claim_Submitted_Date, 112), 5,0,' ')

Answer to Tim Schmelter :

You can compare the methods like this:

declare @i int = 0
declare @dummy varchar(20)
while @i < 1000000
begin
--set @dummy = stuff(convert(varchar(6), getdate() + @i / 1000, 112), 5,0,' ') 
set @dummy = REPLACE(CONVERT(VARCHAR(7), getdate() + @i / 1000, 121), '-', ' ') 
set @i = @i + 1
end

On my server, my version takes 1 second and the other version takes 3 seconds

SQL Server DATEPART for year and month

Earlier versions of SQL Server don't have the FORMAT, so you'd need to so something like

YEAR(Date) = 2015 AND MONTH(Date) = 1

or something like that to check the two conditions

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

Generate month and year from date and store automatically in table

You can use computed columns or trigger.
I recommend to use computed columns. It's very easy to implement and maintenance.

To get year and month use YEAR and MONTH functions

Computed Columns in SQL Server

DateTime functions in SQL Server

Specific day of current month and current year from date time SQL Server 2005

The simplest way to select records from a specific day in the current month and year is to declare a datetime variable assigned to the specified day, month and year, and replace getdate() in your query with the variable - like so:

declare @date datetime 

select @date = '10-Aug-2010'

Select * from client c 
inner join invoice i on c.id = i.ClientID 
WHERE DateDiff(dd, i.date, @date) = 0

EDIT: To run a query for a specified day of the month in the current month, try the following:

declare @day integer

select @day = 10

Select * from client c 
inner join invoice i on c.id = i.ClientID 
WHERE DateDiff(dd, i.date, dateadd(dd,@day-datepart(dd,getdate()),getdate())) = 0

SQL Server 2005 Get First and Last date for any Month in any Year

DECLARE @Month int
DECLARE @Year int

set @Month = 2
set @Year = 2004

select DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0)) /*First*/

select DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0))) /*Last*/

But what do you need as time component for last day of the month? If your datetimes have time components other than midnight you may well be better off just doing something like

WHERE COL >= DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0)) 
     AND COL < DATEADD(month,@Month,DATEADD(year,@Year-1900,0)) 

In this way your code will continue to work if you eventually migrate to SQL Server 2008 and the greater precision datetime datatypes.

---

Related Topics