Show different last 6 Month in format DD-MM-YYYY

Question

Post reply

Show different last 6 Month in format DD-MM-YYYY

SQL_Kills

Hall of Fame

Points: 3145
More actions
May 15, 2015 at 2:57 am

#296971

Hi,
How I want to see the below dates via 6 different Select Statements but I am unsure how to get this?
01-May-2015
01-Apr-2015
01-Mar-2015
01-Feb-2015
01-Jan-2015
01-Dec-2014
I have this Select statement
select replace(convert(char(11),getdate(),113),' ','-')
But it is returning the 15-May-2015 and it should be 01-May-2015 for this select statement
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

SQL_Kills Hall of Fame Points: 3145 More actions · Answer 1

Sorted it now:

Select

'01-' + substring(datename(month,dateadd(mm,-5,getdate())),1,3) + '-' + convert(varchar,datepart(yyyy,dateadd(mm,-5,getdate())))

Select

'01-' + substring(datename(month,dateadd(mm,-4,getdate())),1,3) + '-' + convert(varchar,datepart(yyyy,dateadd(mm,-4,getdate())))

Select

'01-' + substring(datename(month,dateadd(mm,-3,getdate())),1,3) + '-' + convert(varchar,datepart(yyyy,dateadd(mm,-3,getdate())))

Select

'01-' + substring(datename(month,dateadd(mm,-2,getdate())),1,3) + '-' + convert(varchar,datepart(yyyy,dateadd(mm,-1,getdate())))

Select

'01-' + substring(datename(month,dateadd(mm,-1,getdate())),1,3) + '-' + convert(varchar,datepart(yyyy,dateadd(mm,-1,getdate())))

Select

'01-' + substring(datename(month,getdate()),1,3) + '-' + convert(varchar,datepart(yyyy,getdate()))

Ed Wagner SSC Guru Points: 287015 More actions · Answer 2

If you want to do this in a single query and your requirement is that you select the previous 6 months starting with this month, you can do it like this:

SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()) - t.N + 1, 0)

FROM dbo.Tally t

WHERE t.N <= 6

ORDER BY DATEADD(month, DATEDIFF(month, 0, GETDATE()) - t.N + 1, 0) DESC;

Because the query is returning datetime values, you can format your dates however you want.

This uses a tally table to do your counting for you so you don't have to have multiple SELECT statements or a loop. For more information on the tally table, see http://qa.sqlservercentral.com/articles/T-SQL/62867/.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ScottPletcher SSC Guru Points: 100262 More actions · Answer 3

SELECT REPLACE(CONVERT(varchar(11), DATEADD(MONTH, month_adjustment, current_month), 106), ' ', '-') AS column_name

FROM (

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS current_month

) AS get_current_month

CROSS JOIN (

SELECT 0 AS month_adjustment UNION ALL

SELECT -1 UNION ALL SELECT -2 UNION ALL SELECT -3 UNION ALL SELECT -4 UNION ALL

SELECT -5

) AS months_to_process

ORDER BY DATEADD(MONTH, month_adjustment, current_month)

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 4

You could also do this since this a SQL 2008 forum:

SELECT REPLACE(CONVERT(varchar(11), DATEADD(MONTH, month_adjustment, current_month), 106), ' ', '-') AS column_name

FROM (

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS current_month

) AS get_current_month

CROSS JOIN (select month_adjustment from (values (0),(-1),(-2),(-3),(-4),(-5))dt(month_adjustment)) AS months_to_process

ORDER BY DATEADD(MONTH, month_adjustment, current_month);