Generating dates - 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

Generating dates

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-12-07 : 10:04:00

I'm building an online calculator. I have a begin date and an end date where users can put in the range they want to see. How would I get every 14 days (just Friday's) to generate a stored procedure to return the dates I specified?

3/3/2009 to 8/21/2009

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-07 : 10:07:08

make use of F_TABLE_DATE

KH
[spoiler]Time is always against us[/spoiler]

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-12-07 : 10:37:45

Thanks for the link but I've never created a function and called it before. Could you assit with this:

declare
@start datetime,
@end datetime

select @start = '2009-03-07 12:00:00'
@end = '2009-08-21 12:00:00'
From F_Table_DATE(@start, @end)

I am getting incorrect syntax by @end (the last line)

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-07 : 10:38:13

I would say that flashing around a lot of cash should generate A LOT of dates

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

Add yourself!
http://www.frappr.com/sqlteam

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-12-07 : 10:51:02

I agree that's what got Tiger Woods into trouble. He's up to 8 women now!

Okay trying again

I want a user to enter

start date: 07/10/2009

end date: 11/27/2009

I would like all of these dates to appear on the screen when the function runs:

7/10/2009
7/24/2009
8/07/2009
8/21/2009
9/04/2009
9/18/2009
10/02/2009
10/16/2009
10/30/2009
11/13/2009
11/27/2009

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-12-07 : 10:54:03

By the way the results are biweekly (every 14 days) on a Friday.

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-07 : 11:09:07

quote:
Originally posted by JJ297

Thanks for the link but I've never created a function and called it before. Could you assit with this:

declare
@start datetime,
@end datetime

select @start = '2009-03-07 12:00:00'
@end = '2009-08-21 12:00:00'

SELECT *
From F_Table_DATE(@start, @end)
where . . . .

I am getting incorrect syntax by @end (the last line)

KH
[spoiler]Time is always against us[/spoiler]

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-12-07 : 11:22:42

Okay sorry for being a little bit slow I'm not getting it. where do I store the fuction if I don't have a database or a table? Do I put it all in the same stored procedure then just call it in my program like this?

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
create function dbo.F_TABLE_DATE
(
@FIRST_DATE datetime,
@LAST_DATE datetime
)
/*
Function: dbo.F_TABLE_DATE */

declare
@start datetime,
@end datetime

select @start = '2009-03-07 12:00:00'
@end = '2009-08-21 12:00:00'

SELECT *
From F_Table_DATE(@start, @end)
where start= '2009-03-07 12:00:00' and end='2009-08-21 12:00:00'

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-07 : 11:33:37

In post #2, you need to click on the link, the get the function, compile it to your database, and off you go

MEN: Hide your nine iron

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

Add yourself!
http://www.frappr.com/sqlteam

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-12-07 : 11:39:29

Ugh that's my problem I don't have a database or a table. So I don't know what to do? I'm creating an online calculator. I have two boxes on the screen:

Begin date and end date. When a user puts those dates in and hit the submit button I would like for those dates (I guess using the function) to appear on the page.

I hope this makes sense.

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-07 : 11:43:09

well...this being a MICROSOFT SQL SERVER dedicated site....I'm not sure what we can do

What are you writing this in?

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

Add yourself!
http://www.frappr.com/sqlteam

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-12-07 : 11:56:48

I'm using SQL Server 2005. I'm going to code the program using .net and VB.

Okay I think I said it wrong. I found another function someone wrote (they are gone now) but they placed it in a database called District then they are calling it in their stored procedure.

Okay so I want to create the function in the District DB so I right clicked on Functions, selected New, inline table valued function and got this:

Do I create my function as such?



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION dbo.F_TABLE_DATE
(	
	-- Add the parameters for the function here
	@FIRST_DATE		datetime,
	@LAST_DATE		datetime
)
RETURNS TABLE 
AS
RETURN 
(
	-- Add the SELECT statement with parameter references here
	SELECT 0 Don't know what I put here?
)
GO

Then I save it then create a new stored procedure then call the function?

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-07 : 12:24:14

OK, so you want to create a function, but you don't want to create the function listed in post #2

Is that right?

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

Add yourself!
http://www.frappr.com/sqlteam

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-12-07 : 13:57:06

Yes that's correct. I would like to create a function that will give me any startdate or enddate that's specified.

Example:

I would like all of the Friday dates listed between

07/3/2009 - 11/27/2009

Thanks!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-12-07 : 13:58:40

I don't know if F_TABLE_DATE will give me what I want?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-07 : 19:13:59

1. open a query window to database where you want this function to be
2. Copy and paste the F_TABLE_DATE function from the link into the query window and run it
3. open a new query window and run the below code


select	[DATE]
from
(
	select	[DATE], row_no = row_number() over (order by [DATE])
	from	F_TABLE_DATE('20090710', '20091127')
	where	WEEKDAY_NAME	= 'Fri'
) d
where	row_no % 2 = 1

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-08 : 01:32:16

or

declare @start_date datetime,@end_date datetime
select @start_date ='07/10/2009',@end_date='11/27/2009'
select dateadd(week,number*2,@start_date) from master..spt_values
where type='p' and number<=datediff(week,@start_date,@end_date)/2

Madhivanan

Failing to plan is Planning to fail

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-12-08 : 08:02:32

Thanks guys for your replies.

I opened the a new query window
selected the DB I want to put it in:

pasted this:

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[F_TABLE_DATE]')
and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[F_TABLE_DATE]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
create function dbo.F_TABLE_DATE
(
@FIRST_DATE datetime,
@LAST_DATE datetime
)
and getting incorrect syntax near ')' line 5

What am I doing wrong?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-08 : 08:19:24

quote:
Originally posted by JJ297

Thanks guys for your replies.

I opened the a new query window
selected the DB I want to put it in:

pasted this:

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[F_TABLE_DATE]')
and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[F_TABLE_DATE]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
create function dbo.F_TABLE_DATE
(
@FIRST_DATE datetime,
@LAST_DATE datetime
)
and getting incorrect syntax near ')' line 5

What am I doing wrong?

You dont need a function

create procedure proc_geneate_dates
(
@start_date datetime,
@end_date datetime
) 
as
select dateadd(week,number*2,@start_date) as dates from master..spt_values
where type='p' and number<=datediff(week,@start_date,@end_date)/2

GO

Execute the sp by

EXEC proc_geneate_dates '07/10/2009','11/27/2009'

Madhivanan

Failing to plan is Planning to fail

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-12-08 : 08:34:18

Thanks!!!!

Where do I add the convert(varchar, start_date,1) to just bring the date up and not the time?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-08 : 08:39:37

quote:
Originally posted by JJ297

Thanks!!!!

Where do I add the convert(varchar, start_date,1) to just bring the date up and not the time?

In the select statement

select convert(varchar(30),dateadd(week,number*2,@start_date),1) as dates

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources