Calendar Table - 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)

Calendar Table

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-02-25 : 15:25:51

Hello,

I am a little bit confused about something.
I need to create random dates but using a range.

What I need is:
0. Date0 > Get a random date in a range of N years of getdate()

Then get 3 random dates:
1. DateA > random date in a range of -200 days to -100 days of Date0
2. DateB > random date in a range of +100 days to +200 days of Date0
3. DateC > random date in a range of -40 years to -10 years of Date0

After some reading it seems I should use Number and Calendar tables.

I am using:

declare @Numbers table
(
n int identity(1,1) not null primary key clustered
)
while coalesce(scope_identity(), 0) <= 4
begin
insert @Numbers default values
end

INSERT Calendar(dt)
SELECT DATEADD(DAY, Number, '20000101')
FROM dbo.Numbers
WHERE Number <= 10957
ORDER BY Number

Can I do this using these tables?

Is there a good web site with SQL function libraries?

Thanks,
Miguel

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-25 : 15:46:24

You can use the F_RANDOM_DATETIME function on this link:

Random Integer, Sample, and Datetime Functions
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69499


select
	Random_Datetime_1 = 
		[dbo].[F_RANDOM_DATETIME](
		-- 200 days before today
		dateadd(dd,datediff(dd,0,getdate())-200,0),
		-- 100 days before today
		dateadd(dd,datediff(dd,0,getdate())-100,0),
		newid() ),
	Random_Datetime_2 = 
		[dbo].[F_RANDOM_DATETIME](
		-- 100 days after today
		dateadd(dd,datediff(dd,0,getdate())+100,0),
		-- 200 days after today
		dateadd(dd,datediff(dd,0,getdate())+200,0),
		newid() ),
	Random_Datetime_3 = 
		[dbo].[F_RANDOM_DATETIME](
		-- 40 years before today
		dateadd(year,datediff(year,0,getdate())-40,0),
		-- 10 years before today
		dateadd(year,datediff(year,0,getdate())-10,0),
		newid() )


Results:

Random_Datetime_1       Random_Datetime_2       Random_Datetime_3
----------------------- ----------------------- -----------------------
2007-09-11 19:05:23.097 2008-06-17 07:47:08.763 1969-05-11 04:49:00.200

(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources