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

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-02-03 : 11:33:09

Thanks to madhivanan he helped me with this query to get biweekly dates from the date I specified.

@start_date datetime,
@end_date datetime
as
select convert(varchar(30),dateadd(week,number*1,@start_date),1) as Dates from master..spt_values
where type='p' and number<=datediff(week,@start_date,@end_date)
and @start_date=@start_date and @end_date=@end_date

How do I change this to generate all dates from 4/3/2009 - 11/27/2009

and a different one to generate dates from Monday - Friday from 4/3/2009 - 11/27/2009

This would be two separate procedures. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 11:43:11

[code]
...
@start_date datetime,
@end_date datetime,
@Week_Day_Only int = 0
as
SELECT
FROM
(
select dateadd(day,number,@start_date) as Date,
DATENAME(dw,dateadd(day,number,@start_date)) AS DOW
from master..spt_values
where type='p'
and dateadd(day,number,@start_date)<=@end_date
)t
WHERE DOW NOT IN ('Sunday','Saturday')
OR @Week_Day_Only = 0

GO

EXEC procname '2009-04-03','2009-11-27'
to get all days

EXEC procname '2009-04-03','2009-11-27',1

to get only week days
[/code]

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-02-03 : 12:09:58

Thanks. When I tried to create this:

create procedure AllDays
@start_date datetime,
@end_date datetime,
@Week_Day_Only int = 0
as
SELECT
FROM
(
select dateadd(day,number,@start_date) as Date,
DATENAME(dw,dateadd(day,number,@start_date)) AS DOW
from master..spt_values
where type='p'
and dateadd(day,number,@start_date)<=@end_date
)t
WHERE DOW NOT IN ('Sunday','Saturday')
OR @Week_Day_Only = 0

I'm getting the error message:
Msg 156, Level 15, State 1, Procedure AllDays, Line 7
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Procedure AllDays, Line 14
Incorrect syntax near 't'.

What am I doing wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 12:12:03

quote:
Originally posted by JJ297

Thanks. When I tried to create this:

create procedure AllDays
@start_date datetime,
@end_date datetime,
@Week_Day_Only int = 0
as
SELECT Date
FROM
(
select dateadd(day,number,@start_date) as Date,
DATENAME(dw,dateadd(day,number,@start_date)) AS DOW
from master..spt_values
where type='p'
and dateadd(day,number,@start_date)<=@end_date
)t
WHERE DOW NOT IN ('Sunday','Saturday')
OR @Week_Day_Only = 0

I'm getting the error message:
Msg 156, Level 15, State 1, Procedure AllDays, Line 7
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Procedure AllDays, Line 14
Incorrect syntax near 't'.

What am I doing wrong?

Oops missed column name in select
modify like above

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-02-03 : 12:17:31

Thank you that's it!

Question for you...when I'm running the m - f stored procedure why do I put the 1 after the two dates?

ExecuAllDays '2009-04-03','2009-11-27',1

What does the 1 do is the week_day_only?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 12:23:17

quote:
Originally posted by JJ297

Thank you that's it!

Question for you...when I'm running the m - f stored procedure why do I put the 1 after the two dates?

ExecuAllDays '2009-04-03','2009-11-27',1

What does the 1 do is the week_day_only?

passing 1 enforces the where filter and returns only week days

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-02-03 : 12:30:46

Thanks for the explanation and your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 12:32:14

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources