Creating Script that outputs Dates

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

Creating Script that outputs Dates

Author

Topic

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-10-26 : 12:32:28

Hi, I have a general questions on how to create a script that outputs dates . Please let me know if you have any suggestions or questiosn!
thansk much

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-26 : 12:35:39

Is it possible for you to be more clear what you need?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-26 : 12:36:49

Maybe this?
http://www.sqlteam.com/search.aspx?cx=011171816663894899992%3Aaow51lf_dim&cof=FORID%3A9&q=f_table_date#1137

No, you're never too old to Yak'n'Roll if you're too young to die.

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-10-26 : 13:20:24

well basically, what functions should I use to get a table with dates output as a result?

webfred - thanks for sending this link but I think this is way too complicated for my level of knowledge - thanks anyway!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-26 : 13:28:58

Maybe you can give an example for what you want to do and what you need?

No, you're never too old to Yak'n'Roll if you're too young to die.

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-10-26 : 13:44:38

This is part of the problem - I am working in the darkness. Can you please tell me what are some more specific uses of tables/sql data:
For example, export to excel, perform queries..what else? What else can a table outputing dates be used for in general? thanks!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-26 : 13:49:38

If you don't know why you need a date table, why do you think you need one?

CODO ERGO SUM

MrTexasFreedom
Starting Member

13 Posts

Posted - 2010-10-26 : 20:41:56

quote:
Originally posted by VeselaApo

This is part of the problem - I am working in the darkness. Can you please tell me what are some more specific uses of tables/sql data:
For example, export to excel, perform queries..what else? What else can a table outputing dates be used for in general? thanks!

I use date tables all the time. Every once in a while, while I'm at the sql editor, I just pound out a little number like this:

quote:
insert into date_table VALUES (GETDATE())

Just to put the current date somewhere. Then when I am at work and it seems like the day is just dragging on forever, I like to perform this little trick:

quote:
select * from date_table

Makes time pass a lot faster.

So, if it's any help, that's how I use date tables.

MrTexasFreedom

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-10-28 : 12:20:52

Hi, I think I have a better idea what I need now - I need a script that will display all the dates from today to 5 years ahead as well as identify all business days.
I looked at the link that webfred posted but the script there looks very very complicated and I dont know whether I can make it work. Please let me know if you have any suggestions or can reference other places to look at.

thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2010-10-28 : 12:22:47

OK....

1. Are you working with SQL Server, if so what version?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

TimSman
Posting Yak Master

127 Posts

Posted - 2010-10-28 : 12:28:52

I don't have dates anymore. I'm married.
However, in my experience, it's a bad idea to join dates. Chaos ensues.

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-10-28 : 12:35:01

I am using Microsoft sql server 2008 version... I probably just need some sort of loop function?

X002548
Not Just a Number

15586 Posts

Posted - 2010-10-28 : 12:52:12

2. Are you using Microsfot Mangement Studio?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-10-28 : 12:53:48

YES!!! Sorry for being so vague in my answers..

X002548
Not Just a Number

15586 Posts

Posted - 2010-10-28 : 13:04:23

3. Do you have a table you want to get he dates from, and if yes, do you know how to script and post the DDL?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-10-28 : 13:12:57

No - I have to create a temp table that lists all the dates from today to 5 years ahead. I have to create the script from scratch. I already started doing something but the procedure returns many many mistakes...here it is what I did so far:

create table #currentdate
(
C_date DATE
)
Insert into #currentdate (C_date)
values ('2010-10-28')

select * from #currentdate
drop table #currentdate

Create procedure Outputdates ()

Begin
Declare @newdate INT
set @newdate = (select [C_date] from #currentdate)

Loop1:
LOOP
set @newdate = @newdate + 1
update #currentdate SET C_Date = @newdate

end LOOP;
end

The errors I get on this script are:

Msg 102, Level 15, State 1, Procedure Outputdates, Line 1
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Procedure Outputdates, Line 8
Incorrect syntax near 'LOOP'.
Msg 102, Level 15, State 1, Procedure Outputdates, Line 12
Incorrect syntax near 'LOOP'.

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-10-28 : 13:26:34

Here is the updated script that has an end date but it still gives the same errors:( thanks so much for your help!

Create procedure Outputdates (In N_date DATE)

Begin
Declare @newdate INT
set @newdate = (select [C_date] from #currentdate)

Loop1:
LOOP

set @newdate = @newdate + 1
update #currentdate SET C_Date = @newdate
where [C_date] = [N_Date];

if @newdate > '2015-10-28'
then leave loop1;
End if;

end LOOP;

end

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-10-28 : 13:56:55

hmmm the more I look into what I have created, the more I have the feeling that it is absolutely wrong..

@ X002548: Can you suggest any functions/procedures I can use to create this table with dates outputs?

Thanks much!

X002548
Not Just a Number

15586 Posts

Posted - 2010-10-28 : 14:20:16

how about this hack




DECLARE @y int;			SET @y = 5
DECLARE @d1 datetime;	SET @d1 = GETDATE()
DECLARE @d2 datetime;	SET @d2 = DATEADD(yy,@y,GETDATE())

SELECT CONVERT(int,(CONVERT(datetime,@d1))), CONVERT(int,(CONVERT(datetime,@d2)))

SELECT myDay99, CONVERT(datetime,[myDay99]) AS myDatetime
  FROM (
SELECT CONVERT(int,m.n + hth.n + tth.n + th.n + h.n + t.n + u.n) AS [myDay99]
  FROM			(SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION  SELECT '2' AS [n] UNION  SELECT '3' AS [n] UNION  SELECT '4' AS [n] UNION  
				 SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION  SELECT '7' AS [n] UNION  SELECT '8' AS [n] UNION  SELECT '9' AS [n] 
		) AS u
CROSS JOIN		(SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION  SELECT '2' AS [n] UNION  SELECT '3' AS [n] UNION  SELECT '4' AS [n] UNION  
				 SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION  SELECT '7' AS [n] UNION  SELECT '8' AS [n] UNION  SELECT '9' AS [n] 
		) AS t
CROSS JOIN		(SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION  SELECT '2' AS [n] UNION  SELECT '3' AS [n] UNION  SELECT '4' AS [n] UNION  
				 SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION  SELECT '7' AS [n] UNION  SELECT '8' AS [n] UNION  SELECT '9' AS [n] 
		) AS h
CROSS JOIN		(SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION  SELECT '2' AS [n] UNION  SELECT '3' AS [n] UNION  SELECT '4' AS [n] UNION  
				 SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION  SELECT '7' AS [n] UNION  SELECT '8' AS [n] UNION  SELECT '9' AS [n] 
		) AS th
CROSS JOIN		(SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION  SELECT '2' AS [n] UNION  SELECT '3' AS [n] UNION  SELECT '4' AS [n] UNION  
				 SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION  SELECT '7' AS [n] UNION  SELECT '8' AS [n] UNION  SELECT '9' AS [n] 
		) AS tth
CROSS JOIN		(SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION  SELECT '2' AS [n] UNION  SELECT '3' AS [n] UNION  SELECT '4' AS [n] UNION  
				 SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION  SELECT '7' AS [n] UNION  SELECT '8' AS [n] UNION  SELECT '9' AS [n] 
		) AS hth
CROSS JOIN		(SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION  SELECT '2' AS [n] UNION  SELECT '3' AS [n] UNION  SELECT '4' AS [n] UNION  
				 SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION  SELECT '7' AS [n] UNION  SELECT '8' AS [n] UNION  SELECT '9' AS [n] 
		) AS m
) AS XXX
WHERE [myDay99] BETWEEN CONVERT(int,(CONVERT(datetime,@d1))) AND CONVERT(int,(CONVERT(datetime,@d2)))

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-10-28 : 14:23:37

Thanks so much Brett!! It worked! I will take a closer look at the script and see what have you created!

In addition, do you know how I can identify all business dates (mon-Fri) from this table?

Please let me know if you have any ideas! thanks

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-10-28 : 14:25:24

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources