May 15, 2015 at 2:57 am
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
May 15, 2015 at 3:28 am
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()))
May 15, 2015 at 6:01 am
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://www.sqlservercentral.com/articles/T-SQL/62867/.
May 15, 2015 at 10:17 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 15, 2015 at 10:53 am
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);
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply