CODEWITHSUNDEEP
sqlsql-servert-sql
nnnn
nnnn

Reputation: 1051

Select year/month format in a given interval of years


I am finding a simple solution .
I can select months in given interval of years by this-

SELECT DISTINCT DATEPART(month, colDate) AS 'Month'    --result=> 4
FROM MyTable
WHERE YEAR(colDate)>='2012'
AND YEAR(colDate)<='2013'


And, I can select years in given interval of years by this-

SELECT DISTINCT DATEPART(year, colDate) AS 'Year'      --result=> 2012
FROM MyTable
WHERE YEAR(colDate)>='2012'
AND YEAR(colDate)<='2013'

How can I directly select these result in yyyy/MM format? =>2012/04
Is there any way for this problem?
Thanks for your interest and replies.

Upvotes: 0

Views: 262

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

You should find this approach much nicer in terms of execution plan and potential index usage on the colDate column, if one exists, compared to your current approach (most importantly the where clause).

SELECT d = REPLACE(CONVERT(CHAR(7), d, 121), '-', '/')
FROM 
(
  SELECT DISTINCT 
    d = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', colDate), '19000101')
    FROM dbo.MyTable
    WHERE colDate >= '20120101'
    AND colDate < '20140101'
) AS x
ORDER BY d;

Upvotes: 1

Related Questions