Increment a date T-SQL Statement

Question

Increment a date T-SQL Statement

Jnrstevej

SSCrazy

Points: 2343
More actions
June 2, 2011 at 8:50 am

#241628

Hi
Can anyone help me write a store procedure to increment a month
For example
The current month is January 10 I want to show the last 12 months so from January 10 - December 10.
Now if the current month is February 10 I want to increment it to show from February 10 - January 11 and so on
Jan 10, Feb 10, Mar 10, April 10 , May 10, June 10, July 10, Aug 10, Sep 10, Oct 10, Nov 10, Dec 10
Can anyone help me or provide me with similar examples??
Thanks in advance
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)

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

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Is this a homework question? I ask because I answered something almost identical to this a couple of days ago (here). . .

One way would be to do it like this: -

DECLARE @date DATETIME, @number INT

SET @date = '2011-06-06 00:00:00'

SET @number = 12

;WITH CTE AS (

SELECT 0 AS months

UNION ALL

SELECT months + 1

FROM CTE

WHERE months < @number-1)

SELECT DATEADD(MONTH,months,@date)

FROM CTE

You would need to format the result as you require.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jnrstevej SSCrazy Points: 2343 More actions · Answer 2

Hi skcadavre,

Thanks for your reply, its not a homework question i'm in the process of designing a SSRS report and i need this section in the store procedure in order to filter the data been trying to figure it out for ages.

Question I'm assuming you would need to include a convert @set = date to remove the time??

__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

Like so: -

DECLARE @date DATETIME, @number INT

SET @date = '2011-06-06 00:00:00'

SET @number = 12

;WITH CTE AS (

SELECT 0 AS months

UNION ALL

SELECT months + 1

FROM CTE

WHERE months < @number-1)

SELECT DATEADD(MONTH,months,@date),

CONVERT(VARCHAR(10),DATEADD(MONTH,months,@date), 101) AS mmddyyyy,

CONVERT(VARCHAR(10),DATEADD(MONTH,months,@date), 103) AS ddmmyyyy,

CONVERT(VARCHAR(12),DATEADD(MONTH,months,@date), 107) AS MMMddyyyy,

CONVERT(VARCHAR(12),DATEADD(MONTH,months,@date), 113) AS ddMMMyyyy

FROM CTE

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jnrstevej SSCrazy Points: 2343 More actions · Answer 4

Brilliant i can work with that

__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)

drew.allen SSC Guru Points: 76948 More actions · Answer 5

Jnrstevej (6/2/2011)
Hi skcadavre,
Thanks for your reply, its not a homework question i'm in the process of designing a SSRS report and i need this section in the store procedure in order to filter the data been trying to figure it out for ages.
Question I'm assuming you would need to include a convert @set = date to remove the time??

Since you are using SSRS, you should use the formatting available within SSRS to format the datetime field the way you want it.

If you want to eliminate the time for filtering purposes (before it gets to SSRS), using CONVERT() is one of the worst possible ways to approach it. You should use the date/time functions to manipulate date/time data. Specifically, the following two expressions will give you midnight today and midnight on the first of the current month.

SELECT

Cast(Datediff(Day, 0, GetDate()) as datetime) AS MidnightToday

, DateAdd(Month, DateDiff(Month, 0, GetDate()), 0) AS FirstOfCurrentMonth -- at midnight

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

drew.allen (6/2/2011)
Jnrstevej (6/2/2011)
Hi skcadavre,
Thanks for your reply, its not a homework question i'm in the process of designing a SSRS report and i need this section in the store procedure in order to filter the data been trying to figure it out for ages.
Question I'm assuming you would need to include a convert @set = date to remove the time??
Since you are using SSRS, you should use the formatting available within SSRS to format the datetime field the way you want it.

I agree with using SSRS formatting. Any data formatting is a presentation layer task, so I never like to do it in DB.

drew.allen (6/2/2011)
If you want to eliminate the time for filtering purposes (before it gets to SSRS), using CONVERT() is one of the worst possible ways to approach it. You should use the date/time functions to manipulate date/time data. Specifically, the following two expressions will give you midnight today and midnight on the first of the current month.
SELECT
Cast(Datediff(Day, 0, GetDate()) as datetime) AS MidnightToday
, DateAdd(Month, DateDiff(Month, 0, GetDate()), 0) AS FirstOfCurrentMonth -- at midnight
Drew

When you post sweeping comments, you should probably include a test script that proves what you're claiming. Note, I'm not saying you're wrong. As I said before, I use the presentation layer when formatting data so I've never really done any formatting on large data sets, but your sweeping comment without any evidence doesn't change the opinions of anyone.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

drew.allen SSC Guru Points: 76948 More actions · Answer 7

skcadavre (6/3/2011)
When you post sweeping comments, you should probably include a test script that proves what you're claiming. Note, I'm not saying you're wrong. As I said before, I use the presentation layer when formatting data so I've never really done any formatting on large data sets, but your sweeping comment without any evidence doesn't change the opinions of anyone.

I'm not getting paid to post here. I don't feel the need to post test scripts for something that is documented elsewhere and widely known, but if you want to pay me to do so, I will gladly post the requested scripts.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1003893 More actions · Answer 8

skcadavre (6/3/2011)
When you post sweeping comments, you should probably include a test script that proves what you're claiming. {snip}... but your sweeping comment without any evidence doesn't change the opinions of anyone.

Ah... ya got me between a rock and a hard spot here. 😉 I've dedicated a huge amount of time to dispelling SQL myths on the internet, especially when someone claims performance, so I'm right there with you when it comes to any claims of performance. I like to see people prove such statements.

However... not only has it been proven that conversion of dates to VARCHARs is comparatively dreadfully slow, but the method that Drew used is one of the faster methods there is and it will absolutely blow the doors off of any conversion to a VARCHAR using CONVERT. So I can understand why he's reluctant to spend any additional time on what has become well known previously proven fact.

That not withstanding, I'll be happy to prove it because it's a question asked so often that I got tired of writing ad hoc code for it and built a canned example. Drew's method is called DATEDIFF/DATETIME in the following code. Compare it to any of the conversions to VARCHAR and see why he was a bit adamant about it.

/**********************************************************************************************************************

Purpose:

Create a voluminous test table with highly randomized DATETIME data.

--Jeff Moden

**********************************************************************************************************************/

--===== Do this test in a nice safe place that everyone has.

USE TempDB

;

--===== Conditionally drop the test table to make reruns easier

IF OBJECT_ID('dbo.JBMTest','U') IS NOT NULL

DROP TABLE dbo.JBMTest

;

--===== Create and populate a 1,000,000 row test table.

-- "SomeID" has a range of 1 to 1,000,000 unique numbers

-- "SomeDateTimeTime" has a range of >=01/01/2000 and <01/01/2020 non-unique date/times

SELECT TOP 1000000

SomeID = IDENTITY(INT,1,1),

SomeDateTime = RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME)

INTO dbo.JBMTest

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

--===== This will take the display out of the picture so we

-- can measure the true processing time in memory.

DECLARE @BitBucketDATETIME DATETIME

PRINT '========== BASELINE =========='

SET STATISTICS TIME ON

SELECT @BitBucketDATETIME = SomeDateTime