3 dates after 22 days in one year from jan 1

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)

3 dates after 22 days in one year from jan 1

Author

Topic

anumodhc
Starting Member

13 Posts

Posted - 2009-03-05 : 07:35:16

i want to get 3 continuous dates, then after 22 days in one year.
example:-
from january 1st 1,2, and 3rd jan. then after 22 days 25,26, 27 jan.
like that.

Please advise

AnumodH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-05 : 07:43:39

[code]DECLARE @theYear SMALLINT

SET @theYear = 2009

-- Display the dates
SELECT DATEADD(DAY, 24 * v.Number + d.Number, DATEADD(YEAR, @theYear - 1900, 0))
FROM master..spt_values AS v
INNER JOIN master..spt_values AS d ON d.Type = 'P'
AND d.Number < 3
WHERE v.Type = 'P'
AND v.Number < 16[/code]

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

anumodhc
Starting Member

13 Posts

Posted - 2009-03-05 : 08:32:45

Thanks Peso

How to get the same within a date range?

AnumodH

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-05 : 08:49:06

This should work incase you need a range,,

DECLARE	@theYear SMALLINT
DECLARE	@toYear SMALLINT
SET	@theYear = 2009
SET @toYear=2010
-- Display the dates
select * from 
(
SELECT		DATEADD(DAY, 24 * v.Number + d.Number, DATEADD(YEAR, @theYear - 1900, 0)) result
FROM		master..spt_values AS v
INNER JOIN	master..spt_values AS d ON d.Type = 'P'
			AND d.Number < 3
WHERE		v.Type = 'P'
		--AND v.Number < 16
)a where result<convert(varchar,@toYear)+'1231'

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-05 : 08:49:55

sql 2k,

DECLARE @START DATETIME 
DECLARE	@THEYEAR SMALLINT
DECLARE	@I SMALLINT

SET	@THEYEAR = 2009
SET @I=1
SET @START=CONVERT(DATETIME,CONVERT(VARCHAR,@THEYEAR)+'0101')

PRINT @START
WHILE (@START<CONVERT(DATETIME,CONVERT(VARCHAR,@THEYEAR)+'1231'))
BEGIN
	IF (@I%3=0 AND @I<>0)
		SET @START=@START+22
	ELSE
		SET @START=@START+1
	
	PRINT @START
	SET @I=@I+1
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-05 : 09:17:00

quote:
Originally posted by anumodhc

How to get the same within a date range?

What do you mean by a date range?

1. The 1-2-3..25-26-27 sequence starts at an arbitrary date and ends at an arbitrary date?
2. The 1-2-3..25-26-27 always starts at january 1st but you want an arbitrary subset date range from that?

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-05 : 13:10:21

Moved the topic from the Script Library forum. I wasn't sure what forum to use since version info wasn't provided, so I just picked T-SQL 2005 forum.

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

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources