SQL Server – Dynamically Get Last X Months and Format Output

sql serversql-server-2008

Using today's

date, I need to be able to go back x months and output in the format of YYYY_MM

At the moment, I am using:

select 
right('0000' + cast(datepart(year, getdate()) as varchar(4)), 4)
+ '_'
+ right('00' + cast(datepart(month, DATEADD(month, -3, GETDATE())) as varchar(2)), 2)

Which will give me a output of:

2015_08

The issue I am having is that if I were to go back 12 months using:

select 
right('0000' + cast(datepart(year, getdate()) as varchar(4)), 4)
+ '_'
+ right('00' + cast(datepart(month, DATEADD(month, -12, GETDATE())) as varchar(2)), 2)

The output would still be:

2015_11 (notice the year is still the same)

I am using SQL 2008

EDIT: Solution

DECLARE @table12 AS varchar(50)
SET @table12 = 'SMD_' + replace(convert(varchar(7),DATEADD(MONTH, -12, GETDATE()),102), '.', '_')

Best Answer

Well, Aaron's answer is much more thorough than my code snippet.

DECLARE @MonthsBack INT;
SET @MonthsBack = 11;
SELECT REPLACE(CONVERT(VARCHAR(7), 
   DATEADD(MONTH,(-1 * @MonthsBack), GETDATE()), 120),'-','_');

Learn to use the DATE functions such as DATEADD() to move through time. Also, CONVERT gives you options in how you format dates to match the current purpose. (Which could include formatting for other languages.)

Then, if needed, the predefined formats can be further manipulate as needed. Though formatting for printing is best done in the presentation layer.

Related Solutions

SQL Server – Converting varchar Date to Date Format (dd/mm/yy)

I understand your problem to be how to successfully convert the string into a DATE value. An undelimited string of integers is assumed to be Year-Month-Day order. (And, of course, I agree with the comments that dates should be stored in DATE data types in the database.)

Reviewing the MSDN CONVERT documentation does not show a built-in conversion for your string, but it is easy to work around.

http://msdn.microsoft.com/en-us/library/ms187928.aspx -- CAST and CONVERT

I changed the month to 8 to make it easier to double check. Using the CONVERT style option 3, you can do the following:

DECLARE @String VARCHAR(10);
DECLARE @DateValue DATE;
SET @String = '250809'; 

-- Convert your undelimited string DDMMYY into a DATE
-- First: Add / between the string parts.
SET @STRING = SUBSTRING(@String,1,2)+'/'+
     SUBSTRING(@String,3,2)+'/'+SUBSTRING(@String,5,2);
-- Second: Convert using STYLE 3 to get DD/MM/YY interpretation
SELECT @DateValue = CONVERT(Date, @String, 3);

-- Using the DATE 
-- Select the value in default Year-Month-Day
SELECT @DateValue AS DefaultFormat;    
-- Select the value formatted as dd/mm/yy
SELECT CONVERT(VARCHAR(20),@DateValue,3) AS [DD/MM/YY];

The results of the last two selects are:

DefaultFormat
-------------
2009-08-25

DD/MM/YY
--------
25/08/09

To get your DATE formatted in the way you want it, you have to insert the '/' delimiters then use the STYLE 3 in converting from the string to the DATE. (I am sure that there are other workarounds and conversion styles that would work as well.)

Likewise when displaying the DATE as you desire, you need to use STYLE 3

SQL Server 2008 R2 – Optimize Slow Queries When Joining Tables in Two Databases

You've got a bunch of different questions in here, so let's break 'em out individually.

Q: If I join two tables in the same database with the above query, why is it slow?

A: For starters, you're not using a WHERE clause, so SQL Server has to build the complete result set, merging both tables together. If you only need a subset of the data, consider using a WHERE clause to just get the data you need.

Once you've done that, note that you're using a LEFT OUTER JOIN. This tells SQL Server, "Not all of the table1 records will have matching records in table2." That's totally fine if it's true - but if you know all t1 records will have at least one t2 record, use an INNER JOIN instead.

Next, indexing starts to come into play - depending on the width of the tables and the numbers of fields, you may want to add indexes on the fields you're using for the join. To get good advice on that, it's best to post the actual execution plan you're working with.

Q: If I the tables are in different databases on the same SQL Server, does that change anything?

A: No. There's some interesting gotchas around things like default isolation levels in different databases, but for the most part, your queries should produce the same execution plans and speeds.

Q: Should I use table partitioning to make this go faster?

A: You mentioned database partitioning, but there's no such thing in SQL Server - I'm guessing you meant table partitioning. Generally speaking, no, I wouldn't jump to database design changes in order to make a join go faster. Start with the basics - understanding SQL Server execution plans - and only make database design changes to solve problems that you can't fix with things like indexes.

EDIT: Solution

Best Answer

Related Solutions

SQL Server – Converting varchar Date to Date Format (dd/mm/yy)

SQL Server 2008 R2 – Optimize Slow Queries When Joining Tables in Two Databases

Related Question