doubt in date function - 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

General SQL Server Forums

New to SQL Server Programming

doubt in date function

Author

Topic

kodumudisadha
Starting Member

33 Posts

Posted - 2008-12-03 : 02:34:53

Hi,
the below calender query taking only 01-01-2008 to 12-09-2008.what change need for taking upto yesterday date in this.

SELECT DATEADD(DAY, number, DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)) AS missingDate
FROM master..spt_values
WHERE Type = 'P'
AND number < DATEDIFF(DAY,DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0), DATEADD(year, 1, DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-03 : 02:43:28

[code]SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), number) AS missingDate
FROM master..spt_values
WHERE Type = 'P'
AND number < DATEPART(DAY, GETDATE() - 1)[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 02:44:23

[code]
SELECT DATEADD(DAY, number, DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)) AS missingDate
FROM master..spt_values
WHERE Type = 'P'
AND DATEADD(DAY, number, DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0))<= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-1) [/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-03 : 02:44:54

If you are using SQL Server 2000, you can only have a sequence with 256 days which by chance is September 12.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 02:46:10

thats because spt_values contains only that much values for type='p' in 2000

kodumudisadha
Starting Member

33 Posts

Posted - 2008-12-03 : 03:29:32

ok.i dont want from year starting.i want from today to 256 days back.how to change this.
please help me...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-03 : 03:36:35

[code]SELECT DATEADD(DAY, DATEDIFF(DAY, number, GETDATE()), 0) AS theDate
FROM master..spt_values
WHERE Type = 'P'[/code]

E 12°55'05.63"
N 56°04'39.26"

kodumudisadha
Starting Member

33 Posts

Posted - 2008-12-03 : 03:48:25

Thanks for all its working.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-03 : 06:13:50

quote:
Originally posted by kodumudisadha

ok.i dont want from year starting.i want from today to 256 days back.how to change this.
please help me...

SELECT DATEADD(DAY, DATEDIFF(DAY, number, GETDATE()), 0) AS theDate
FROM master..spt_values
WHERE Type = 'P' AND DATEADD(DAY, DATEDIFF(DAY, number, GETDATE()), 0) > DATEADD(DAY,-256,GETDATE())

Try This U Will get the from today to 256 back of days list

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources