days - 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

days

Author

Topic

gavakie
Posting Yak Master

221 Posts

Posted - 2008-06-09 : 13:44:33

How would I bring back a column that shows all the days from the begining of the year to the current date. Also make sure this moves onto the next year.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-09 : 13:48:56

[code]DECLARE @StartDate datetime

SET @StartDate=CAST('1/1/'+CAST(YEAR(GETDATE()) AS varchar(4)) AS datetime)

SELECT DATEADD(dd,number,@StartDate)
FROM master..spt_values
WHERE type='p'
AND DATEADD(dd,number,@StartDate)<=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
[/code]

gavakie
Posting Yak Master

221 Posts

Posted - 2008-06-09 : 14:14:36

so what if i want numbers all the dates between the first of this year and through 12/31 of this year

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-09 : 14:19:42

[code]DECLARE @StartDate datetime,@EndDate datetime

SELECT @StartDate=CAST('1/1/'+CAST(YEAR(GETDATE()) AS varchar(4)) AS datetime),
@EndDate =CAST('12/31/'+CAST(YEAR(GETDATE()) AS varchar(4)) AS datetime)

SELECT DATEADD(dd,number,@StartDate)
FROM master..spt_values
WHERE type='p'
AND DATEADD(dd,number,@StartDate)<=@EndDate[/code]

gavakie
Posting Yak Master

221 Posts

Posted - 2008-06-09 : 14:53:52

the last date keeps stopping on 9-12-2008

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-09 : 15:07:55

What do these queries show:

SELECT GETDATE()

SELECT COUNT(*) FROM master..spt_values

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

gavakie
Posting Yak Master

221 Posts

Posted - 2008-06-09 : 15:13:04

2008-06-09 13:12:46.920
and
730

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-09 : 15:16:03

Oops, add WHERE type = 'p' to the COUNT(*) query. I'm not sure why that's in the query though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-09 : 15:23:20

In SQL Server 2000, you would have only 256 rows for type='p'. You should have a number table or use this trick


SELECT DATEADD(dd,number,@StartDate)
FROM 
(
	select number from master..spt_values
	WHERE type='p'
	union all
	select number+256 from master..spt_values
	WHERE type='p'
) as s
where DATEADD(dd,number,@StartDate)<=@EndDate

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-06-09 : 16:53:43

You could use the date table function on the link below.

Date Table Function F_TABLE_DATE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

select
	a.[DATE]
from
	F_TABLE_DATE('20080101','20081231') a
order by
	a.[DATE]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources