Days in a year - 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)

Days in a year

Author

Topic

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-06-20 : 05:17:15

Dear gurus..,

I need to get all the days in a year in a single query.

Thanks in advance.,

cool...,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 05:22:01

[code]DECLARE @Year int,@StartDate datetime

SET @Year=2008
SET @StartDate='01/01/'+cast(@year as char(4))
SELECT DATEADD(d,number,@StartDate)
FROM master..spt_values
WHERE Type='p'
AND YEAR(DATEADD(d,number,@StartDate))=@Year[/code]

please note that this works correctly only in SQL 2005

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-06-20 : 05:26:15

Hi vishak,

i want to get the dates without refering the table

cool...,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 05:29:30

quote:
Originally posted by dass05555

Hi vishak,

i want to get the dates without refering the table

cool...,

its just a count table which is present in master. If you want to generate dates wthout using this. you need to make a count table yourself and use it instead of spt_values.

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-06-20 : 05:32:25

My requirement is to list the 15 business days from current date without using any table .

cool...,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 05:42:51

quote:
Originally posted by dass05555

My requirement is to list the 15 business days from current date without using any table .

cool...,

Why didnt you specify this in beginning?

if you dont want to use table then use cte

with date_cte (date,level)
as
(
select dateadd(d,1,getdate()),0
union all
select dateadd(d,1,date) ,level + 1 from date_cte where level <15
)

select * from date_Cte

or while loop

declare @i int
set @i=1
while @i<16
begin
select dateadd(d,@i,getdate())
set @i=@i+1
end

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-06-20 : 07:12:50

Thanks visakh

cool...,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 15:01:38

quote:
Originally posted by dass05555

Thanks visakh

cool...,

You're welcome

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 15:08:36

That will not give you last 15 business days. Both examples will get you all dates.

See other answers here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105208
or try this code

SELECT		TOP 15
		DATEADD(DAY, Delta, DATEDIFF(DAY, '19000101', GETDATE()))
FROM		(
			SELECT	0 AS Delta UNION ALL
			SELECT	-1 UNION ALL
			SELECT	-2 UNION ALL
			SELECT	-3 UNION ALL
			SELECT	-4 UNION ALL
			SELECT	-5 UNION ALL
			SELECT	-6 UNION ALL
			SELECT	-7 UNION ALL
			SELECT	-8 UNION ALL
			SELECT	-9 UNION ALL
			SELECT	-10 UNION ALL
			SELECT	-11 UNION ALL
			SELECT	-12 UNION ALL
			SELECT	-13 UNION ALL
			SELECT	-14 UNION ALL
			SELECT	-15 UNION ALL
			SELECT	-16 UNION ALL
			SELECT	-17 UNION ALL
			SELECT	-18 UNION ALL
			SELECT	-19
		) AS d
WHERE		DATENAME(WEEKDAY, DATEADD(DAY, Delta, DATEDIFF(DAY, '19000101', GETDATE()))) NOT IN ('Saturday', 'Sunday')
ORDER BY	Delta DESC

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 15:20:48

quote:

Originally posted by Peso

That will not give you last 15 business days. Both examples will get you all dates.

See other answers here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105208
or try this code

SELECT		TOP 15
		DATEADD(DAY, Delta, DATEDIFF(DAY, '19000101', GETDATE()))
FROM		(
			SELECT	0 AS Delta UNION ALL
			SELECT	-1 UNION ALL
			SELECT	-2 UNION ALL
			SELECT	-3 UNION ALL
			SELECT	-4 UNION ALL
			SELECT	-5 UNION ALL
			SELECT	-6 UNION ALL
			SELECT	-7 UNION ALL
			SELECT	-8 UNION ALL
			SELECT	-9 UNION ALL
			SELECT	-10 UNION ALL
			SELECT	-11 UNION ALL
			SELECT	-12 UNION ALL
			SELECT	-13 UNION ALL
			SELECT	-14 UNION ALL
			SELECT	-15 UNION ALL
			SELECT	-16 UNION ALL
			SELECT	-17 UNION ALL
			SELECT	-18 UNION ALL
			SELECT	-19
		) AS d
WHERE		DATENAME(WEEKDAY, DATEADD(DAY, Delta, DATEDIFF(DAY, '19000101', GETDATE()))) NOT IN ('Saturday', 'Sunday')
ORDER BY	Delta DESC

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

sorry missed that part here

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources