sql to insert weekend between two 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

sql to insert weekend between two dates

Author

Topic

orlando876
Starting Member

15 Posts

Posted - 2010-07-29 : 13:02:23

Base on a select statement that retrived two dates i need to insert into another table all weekend between these two dates or any other dates or to display the week ends betwwen these two dates

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-29 : 13:07:24

DDLS, SAMPLE DATA AND EXPECTED RESULTS WOULD HELP US

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

orlando876
Starting Member

15 Posts

Posted - 2010-07-29 : 13:25:04

for example need to find all weekends between inclusive 08/14/2010 09/19/2010 and insert those weekends in another table true sql

Celko
Starting Member

23 Posts

Posted - 2010-07-29 : 13:38:49

Base on a select statement that retrived two dates i need to insert into another table all weekend between these two dates or any other dates or to display the week ends betwwen these two dates
[/quote]

Use a calendar table and a BETWEEN predicate. If you don't know the technique, we can talk about it after you post some DDL. Also, start using the ISO-8601 date format (yyyy-mm-dd) instead of dialect.

Author of
SQL for Smarties
Thinking in Sets
SQL Puzzles and Answers
Analytics and OLAP in SQL
SQL Programming Style
Standards in SQL
Data & Databases
Trees & Hierarchies in SQL

orlando876
Starting Member

15 Posts

Posted - 2010-07-29 : 13:43:14

not sure what is the ddl are u saying i need to have a date table i dont have any code i just want to achive the propose objective

Celko
Starting Member

23 Posts

Posted - 2010-07-29 : 14:08:41

quote:
Originally posted by orlando876

not sure what is the DDL are you saying I need to have a date table I dont have any code I just want to achive the propose objective

DDL is the Data Definition Language in SQL. It creates schema objects like tables. What did this SELECT statement you also never showed us work with? Why are you inserting into another table instead of using a VIEW? Etc.

Here is a skeleton that you can figure out:

SELECT C.cal_date
FROM Calendar AS C, MysteryTable AS M
WHERE C.cal_date BETWEEN M.something_start_date AND M. something
AND dayofweek_code IN (0,7); -- iso standards

Author of
SQL for Smarties
Thinking in Sets
SQL Puzzles and Answers
Analytics and OLAP in SQL
SQL Programming Style
Standards in SQL
Data & Databases
Trees & Hierarchies in SQL

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-29 : 14:28:24

Here


DECLARE @s datetime, @e datetime, @n int
SELECT @s = '2010-07-01', @e = '2010-08-31'

DECLARE @d table (n int)
SET @n = DATEPART(DAYOFYEAR, @s) 
WHILE @n < DATEPART(DAYOFYEAR, @e) + 1 
  BEGIN
	INSERT INTO @d(n) SELECT @n 
	SET @n = @n + 1
  END

SELECT * FROM @d WHERE DATEPART(WEEKDAY,n) IN (1,7)

Gotta go to a meeting..ugh

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

orlando876
Starting Member

15 Posts

Posted - 2010-07-30 : 10:41:25

thanks for you help t his i think will get the result i wanted with a few modification

DECLARE @s datetime, @e datetime,@newdate datetime, @n int, @p int
SELECT @s = '2010-07-13', @e = '2010-08-31'

DECLARE @d table (n int)

SET @n = DATEPART(DAYOFYEAR, @s)
SET @p = DATEPART(DAYOFYEAR, @s)
print @n

WHILE @n < DATEPART(DAYOFYEAR, @e) + 1
BEGIN
INSERT INTO @d(n) SELECT @n
set @newdate = DATEADD(day,(@n-@p),@s)
if DATEPART(WEEKDAY,@newdate) in ( 1,7)
begin

print @newdate
end
print @n
SET @n = @n + 1
END

SELECT * FROM @d WHERE DATEPART(WEEKDAY,n) IN (1,7)

print DATEPART(WEEKDAY,@p)
print DATEPART(month,@p)
print DATEPART(day,@p)

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-02 : 14:14:22

ummm...did you run my code?

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-03 : 03:47:10

quote:
Originally posted by Celko

Base on a select statement that retrived two dates i need to insert into another table all weekend between these two dates or any other dates or to display the week ends betwwen these two dates

Use a calendar table and a BETWEEN predicate. If you don't know the technique, we can talk about it after you post some DDL. Also, start using the ISO-8601 date format (yyyy-mm-dd) instead of dialect.

Author of
SQL for Smarties
Thinking in Sets
SQL Puzzles and Answers
Analytics and OLAP in SQL
SQL Programming Style
Standards in SQL
Data & Databases
Trees & Hierarchies in SQL

In SQL Server, you need to use time part with Time seperator T or use YYYYMMDD. For more informations refer
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources