Date calculation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Date calculation

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2008-05-20 : 09:48:19

I want all dates from DATEADD(DAY,-90,GETDATE()).

1/1/2008
1/2/2008
1/3/2008 .....

and so on. This is probably easy, but I can't figure it out. Any help would be appreciated. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-20 : 09:59:44

SELECT DateColumn FROM YourTable WHERE DateColumn > DATEADD(DAY,-90,DATEADD(d,DATEDIFF(d,0,GETDATE()),0))

osupratt
Posting Yak Master

238 Posts

Posted - 2008-05-20 : 10:11:21

I'm trying to create a column of my own that is nothing more than a 'rolling' date from what ever point in time that I want. I'm not trying to pull in dates from another column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-20 : 10:19:41

quote:
Originally posted by osupratt

I'm trying to create a column of my own that is nothing more than a 'rolling' date from what ever point in time that I want. I'm not trying to pull in dates from another column.

Ok. Then try this:-

DECLARE @StartDate datetime
SET @StartDate=DATEADD(DAY,-90,DATEADD(d,DATEDIFF(d,0,GETDATE()),0))

SELECT DATEADD(dd,number,@StartDate)
FROM master..spt_values
WHERE type='p'
AND DATEADD(dd,number,@StartDate)< GETDATE()

osupratt
Posting Yak Master

238 Posts

Posted - 2008-05-20 : 11:18:20

You are the greatest! Exactly what I wanted. Thanks allot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-20 : 11:21:44

quote:
Originally posted by osupratt

You are the greatest! Exactly what I wanted. Thanks allot.

You're welcome

osupratt
Posting Yak Master

238 Posts

Posted - 2008-05-21 : 09:49:52

I'm sorry. I do need a bit more help. I'm trying to get this rolling date into a view and it won't allow the declare statements. Could someone help me to get this rolling date I'm seeking somehow in a view? Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-21 : 09:53:57

quote:
Originally posted by osupratt

I'm sorry. I do need a bit more help. I'm trying to get this rolling date into a view and it won't allow the declare statements. Could someone help me to get this rolling date I'm seeking somehow in a view? Thanks.

create view populate_dates
as
SELECT DATEADD(dd,number,DATEADD(DAY,-90,DATEADD(d,DATEDIFF(d,0,GETDATE()),0)))
FROM master..spt_values
WHERE type='p'
AND DATEADD(dd,number,DATEADD(DAY,-90,DATEADD(d,DATEDIFF(d,0,GETDATE()),0)))< GETDATE()

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 10:07:49

[code]SELECT DATEADD(DAY, -Number, DATEDIFF(DAY, '19000101', GETDATE()))
FROM master..spt_values
WHERE Type = 'P'
AND Number < 91[/code]

I think INDEX SEEK is better then INDEX SCAN.

E 12°55'05.25"
N 56°04'39.16"

osupratt
Posting Yak Master

238 Posts

Posted - 2008-05-21 : 10:27:09

Thanks to both of you. Both worked. Could you explain the Index Seek versus Index Scan? Optimization?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 10:36:00

Turn on "Include Actual Execution Plan" and put the two queries in same query window and run them both.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 11:07:53

Other ways to present the date information

SELECT	CONVERT(CHAR(8), GETDATE() - Number, 1) AS the1,
	CONVERT(CHAR(10), GETDATE() - Number, 103) AS the103,
	CONVERT(CHAR(12), GETDATE() - Number, 107) AS the107,
	DATEADD(DAY, -Number, DATEDIFF(DAY, '19000101', GETDATE())) AS theDate
FROM	master..spt_values
WHERE	Type = 'P'
	AND Number < 91

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources