CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Sven Müller
Sven Müller

Reputation: 131

Convert datetime to month in number and year

I have a datetime e.g. 2014-04-24 00:28:53.897 and want to convert this in a select query to month in number and year - 4 2014.

Upvotes: 5

Views: 45740

Answers (7)

xerius
xerius

Reputation: 94

/* sql code */
--if you have datetime as text use this to convert to date
--select cast('2014-04-24 00:28:53.897' as date)
select convert(varchar(2), month(cast('2014-04-24 00:28:53.897' as date))) + ' ' + convert(char(4), year(cast('2014-04-24 00:28:53.897' as date)))

--if you have datetime field already use this
select convert(varchar(2), month(getdate())) + ' ' + convert(char(4), year(getdate()))

Upvotes: 2

Neeraj Sharma
Neeraj Sharma

Reputation: 588

Set into a format. declare @d datetime='2014-04-24 00:28:53.897' select FORMAT(@d, 'MM yyyy')

For more information, visit here

Upvotes: 2

Rahul Parit
Rahul Parit

Reputation: 331

You can use the following on SQL Server 2012 and above (or others that provide CONCAT):

SELECT CONCAT(DATEPART(mm,dateField),' ',DATEPART(yyyy,dateField))
AS MONTH_YEAR
FROM TABLENAME;

Upvotes: 2

I A Khan
I A Khan

Reputation: 8839

Use DATEPART For this

SELECT Convert(VARCHAR(10),DATEPART(mm,yourfield),111) + '  ' + 
Convert(VARCHAR(10),DATEPART(yyyy,yourfield),111) AS outputmonthyear
FROM yourtableName

http://sqlfiddle.com/#!6/fa887/7

DATEPART Definition and Usage

The DATEPART() function is used to return a single part of a date/time, such as year, month, day, hour, minute, etc.

Syntax DATEPART(datepart,date) Where date is a valid date expression.

for more details please Visit

http://www.w3schools.com/sql/func_datepart.asp

Upvotes: 1

Chanom First
Chanom First

Reputation: 1136

Try this 

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

replace getdate with you datetime column

Hope it help you.

Upvotes: 9

Matt
Matt

Reputation: 15071

Use DATEPART

SELECT CONCAT(DATEPART(mm,datefield),' ',DATEPART(yyyy,datefield)) AS monthyear
FROM yourtable

SQL FIDDLE: http://sqlfiddle.com/#!6/9c896/7/0

Upvotes: 4

user4622594
user4622594

Reputation:

you can use something like this: (where you replace GETDATE() with your date/datetime column)

SELECT CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR(2)) + ' ' + CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR(4))

Upvotes: 2

Related Questions