retrieve only month and year (last two digits) part of datetime field

Question

retrieve only month and year (last two digits) part of datetime field

polkadot

SSChampion

Points: 13664
More actions
May 2, 2012 at 10:17 am

#268278

I found this statement on line for retrieving only MON-YYYY portion of datetime field.
SELECT
CONVERT(CHAR(4), datetime, 100) + CONVERT(CHAR(4), datetime, 120) as Month
FROM myTable;
how do I modify it to retrieve only MON-YY portion of datetime field?
If date time is 2011-10-07 13:18:48.720 ------> I need Oct-11
If date time is 2011-07-26 15:46:16.407 -------> I need Jul-11.
--Quote me

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

This type of formatting really belongs in the front end and not in t-sql. However if you have to format it from sql you will have to make liberal use of date parts and convert. See the list of functions available here. http://msdn.microsoft.com/en-us/library/ms186724.aspx

Meant to include a link to Convert also. http://msdn.microsoft.com/en-us/library/ms187928.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Something like this should work...

declare @Date datetime = '2011-10-07 13:18:48.720'

select LEFT(CAST(@Date as varchar(20)), 3) + '-' + CAST(YEAR(@Date) as CHAR(4))

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

polkadot SSChampion Points: 13664 More actions · Answer 3

Good advice. So, you say, import the whole date and modify in Excel.

Can you give search terms or just a trickle more advice on doing that in front end if I am using Excel? The datetime is headed for the Slicer, is all I know.

--Quote me

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Sure Excel has this type of formatting built in. Just right click -> format Cells -> chose your desired format. You can do this on a whole column in 1 step if you want.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

polkadot SSChampion Points: 13664 More actions · Answer 5

the other issue I have with using front-end is that if the value doesn't exist then the slicer won't create a placeholder for it. That is, if there are no items purchased in month of April, my slicer will show all months except April, which isn't acceptable from a visual standpoint. Any suggestions?

--Quote me

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Not really sure what you mean but can you use ISNULL in your sql? That way you will get a default date if there is no date.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

polkadot SSChampion Points: 13664 More actions · Answer 7

This CAST solution retrieve the whole year YYYY. I need YY.

--Quote me

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

polkadot (5/2/2012)
This CAST solution retrieve the whole year YYYY. I need YY.

So get the right 2.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

polkadot SSChampion Points: 13664 More actions · Answer 9

aRe you Kidding ME? Like this?

, LEFT(CAST(System_CreatedDate as varchar(20)), 3) + '-' + RIGHT(CAST(YEAR(System_CreatedDate) as CHAR(4)), 2) as MonYr

Thanks.:-P

--Quote me

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Not sure what you would want to do for null values, but this should work for actual date values.

DECLARE @DateVar DATETIME;

SET @DateVar = '20120802 10:30:58.003';

SELECT @DateVar, LEFT(DATENAME(mm,@DateVar),3) + '-' + RIGHT(CAST(YEAR(@DateVar) AS VARCHAR),2);

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

LOL. did that get it for you then?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

polkadot SSChampion Points: 13664 More actions · Answer 12

Yes Sean, that worked. Lynn, yours also works.

In my case there will be no NULLs because this column is system generated when the item with the timestamp is created.

Thank you!!! Just in the nic of time!

--Quote me

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 13

How about this?

Sample data:

declare @Date table ( dt datetime)

insert @Date select '2011-10-07 13:18:48.720'

insert @Date select '2011-07-26 15:46:16.407'

insert @Date select '2011-12-26 15:46:16.407'

insert @Date select '2011-01-07 13:18:48.720'

insert @Date select '2009-02-26 15:46:16.407'

insert @Date select '2011-03-26 15:46:16.407'

insert @Date select '2012-04-26 15:46:16.407'

insert @Date select '2012-05-07 13:18:48.720'

insert @Date select '2012-06-26 15:46:16.407'

insert @Date select '2010-09-26 15:46:16.407'

;

T-SQL code:

SELECT d.dt

,CrsApp.d

,[MMM YY] = LEFT(CrsApp.d ,3) + RIGHT (CrsApp.d ,2)

FROM @Date d

CROSS APPLY (SELECT CONVERT(VARCHAR(20), d.dt,107)) CrsApp (d)

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 14

Lynn Pettis (5/2/2012)
Not sure what you would want to do for null values, but this should work for actual date values.
DECLARE @DateVar DATETIME;
SET @DateVar = '20120802 10:30:58.003';
SELECT @DateVar, LEFT(DATENAME(mm,@DateVar),3) + '-' + RIGHT(CAST(YEAR(@DateVar) AS VARCHAR),2);

Save the extra "CAST". Use DATENAME on the Year, as well. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)