Increment a date T-SQL Statement

Question

Increment a date T-SQL Statement

Viewing 10 posts - 16 through 24 (of 24 total)

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

WayneS SSC Guru Points: 95409 More actions · Answer 1

Craig Farrell (6/4/2011)
If Jeff is willing to put his name on the line, something he considers quite valuable, to prove a point with numbers... it's worth listening to.

+1000.

+ more if you actually follow his advice, and not just listen.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 2

WayneS (6/4/2011)
Craig Farrell (6/4/2011)
If Jeff is willing to put his name on the line, something he considers quite valuable, to prove a point with numbers... it's worth listening to.
+1000.
+ more if you actually follow his advice, and not just listen.

Heh... Ok. After it gets dark tonight, face in the direction of Detroit. See the Red glare in the sky? That's me blushing. :blush: You folks are too kind. Thank you for the gift of confidence.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

m_cg SSC Enthusiast Points: 115 More actions · Answer 3

If I understand you correctly, use the dateadd function and subtract 1 from the number of months you wish to add.

DECLARE @date DATETIME, @number INT

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

SET @number = 12

select DATEADD(month , @number-1, @date)

gives '2011-12-06 00:00:00'

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 4

m_cg (6/6/2011)
If I understand you correctly, use the dateadd function and subtract 1 from the number of months you wish to add.
DECLARE @date DATETIME, @number INT
SET @date = '2011-01-06 00:00:00'
SET @number = 12
select DATEADD(month , @number-1, @date)
gives '2011-12-06 00:00:00'

Absolutely correct. Now do it for the 12 months requested and try to avoid the loop which is nearly as important as the question itself. If you can't quite figure it out, post back and I'll show you a couple of ways to do it without the penalty of recursion.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

m_cg SSC Enthusiast Points: 115 More actions · Answer 5

Jeff,

Still not sure of the format, but gave it 2 ways. Does this work for you?

DECLARE @date DATETIME, @number INT

SET @date = '2010-01-01 00:00:00'

--SET @number = 12

drop table monthcounter

create table monthcounter(monthid int not null)

insert into monthcounter(monthid)

values

(1),

(2),

(3),

(4),

(5),

(6),

(7),

(8),

(9),

(10),

(11),

(12)

-- if each month year combination can be in its own row

selectcast(DATEADD(month , monthid-1, @date) as varchar(3)) + ' ' + Right(cast(year(DATEADD(month , monthid-1, @date)) as varchar(4)), 2) IncludedMonYrs

frommonthcounter

order by monthid

-- If you want them as columns

SELECT 'Months to Include' AS month_order,

[0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11]

FROM

(SELECT monthid-1 monthid2, cast(DATEADD(month , monthid-1, @date) as varchar(3)) + ' ' + Right(cast(year(DATEADD(month , monthid-1, @date)) as varchar(4)), 2) IncludedMonYrs

FROM monthcounter) AS SourceTable

PIVOT

(

min(IncludedMonYrs)

FOR monthid2 IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11])

) AS PivotTable;

drew.allen SSC Guru Points: 76944 More actions · Answer 6

m_cg (6/7/2011)
Still not sure of the format, but gave it 2 ways. Does this work for you?
[...]
insert into monthcounter(monthid)
values
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10),
(11),
(12)

Since this syntax was introduced in SQL 2008 and this is a SQL 2005 forum, NO, this does not work.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

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

m_cg (6/7/2011)
Jeff,
Still not sure of the format, but gave it 2 ways. Does this work for you?

What Jeff's talking about is using a Tally table.

e.g.

DECLARE @date DATETIME, @number INT

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

SET @number = 12

SELECT CAST(Datediff(DAY, 0, Dateadd(MONTH, n - 1, @date)) AS DATETIME)

FROM operations.tally

WHERE n <= @number

Which is far superior to the answer I suggested previously (thanks Jeff and Drew for the lesson).

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/

m_cg SSC Enthusiast Points: 115 More actions · Answer 8

jeff article, or one of them, on Tally tables is at URL: http://www.sqlservercentral.com/articles/T-SQL/62867/

I am look forward to going through it. This looks like a nice solution.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 9

skcadavre (6/8/2011)
m_cg (6/7/2011)
Jeff,
Still not sure of the format, but gave it 2 ways. Does this work for you?
What Jeff's talking about is using a Tally table.
e.g.
DECLARE @date DATETIME, @number INT
SET @date = '2011-06-06 00:00:00'
SET @number = 12
SELECT CAST(Datediff(DAY, 0, Dateadd(MONTH, n - 1, @date)) AS DATETIME)
FROM operations.tally
WHERE n <= @number
Which is far superior to the answer I suggested previously (thanks Jeff and Drew for the lesson).

Exactly. Well done! 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

chetan Newbie Points: 1 More actions · Answer 10

hi all,

can any one help me to write query for subtracting 7 days from date,

in that column all date are same in every row , upto 53 times.

i want to subtract 7 days from first row of date upto 53 times.

eg: id date

1 2019-08-02

2 2019-08-02

upto 53 times

expected result :

id date

1 2019-08-02

2 2019-07-26

3 2019-07-19

53 2018-08-02

please help