December 6, 2012 at 3:54 am
Hi All,
I have two dates in sql
Start -11/05/2012 (start of the week)
End -11/26/2012(strat of the week)
I need to create a temp table that would contain the start of the week between two given dates...
As the paramater is passed,the results to be available are:
11/05/2012
11/12/2012
11/26/2012
How to get the start of the week between two given dates in sql
Thanks!
December 6, 2012 at 4:14 am
I'm assuming that the start of the week is Monday. Also this solution uses a tally table.
First create a tally table (numbers table)
If exists (select 1 from information_schema.tables where table_name = 'Tally')
drop table dbo.[Tally]
GO
CREATE TABLE dbo.Tally (N INT,CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N))
DECLARE @Counter INT
SET @Counter = 0
WHILE @Counter <= 10000
BEGIN
INSERT INTO dbo.Tally (N)
VALUES (@Counter)
SET @Counter = @Counter + 1
Not the most elegant solution but try it and see what you get (please note that I use the British date format which is dd/mm/yyyy)
declare @StartDate datetime
declare @EndDate datetime
set @StartDate = '2012-11-05'
set @EndDate = '2012-11-26'
select @StartDate+N
from Tally
where datename(dw, @StartDate+N) = 'Monday'
and @StartDate+N <=@EndDate
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
December 6, 2012 at 4:20 am
try this...
with cte1(date1)
as
(
select convert(date,'2012-11-05')
union all
select DATEADD(week,1,date1) from cte1
where date1<='2012-11-20'
)
select * from cte1
December 6, 2012 at 4:32 am
k.thanigaivel (12/6/2012)
try this...with cte1(date1)
as
(
select convert(date,'2012-11-05')
union all
select DATEADD(week,1,date1) from cte1
where date1<='2012-11-20'
)
select * from cte1
What happens if the start date parameter is not a monday?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
December 6, 2012 at 4:38 am
Create yourself a DIM_DATE table (I still wonder why a lot of people don't have such a table as a default) and then run the following code in your SP
SELECT FULLDATE --COUNT(DATEKEY) (count if you want just a count)
FROM DIM_DATE
WHERE DayNameOfWeek = datename(dw, @StartDate)
AND FULLDATE BETWEEN @StartDate AND @EndDate
(keep in mind that BETWEEN is inclusive - i.e. if @StartDate or @EndDate are of the day you are looking for, then these will be counted too - if that's not what you want, then you can use the greater than (>) and less than (<) operators)
For more information about DIM_DATE table (which is used as a tally table as per earlier post), see:
http://www.kimballgroup.com/data-warehouse-and-business-intelligence-resources/data-warehouse-books/booksmdwt/ (go to Chapter 7—Design and Develop the ETL System; date dimension) - Direct Link for DDL and sample data:
http://www.kimballgroup.com/wp-content/uploads/2012/07/Ch07_Date_Dim_2000-2020.xlsx
HTH,
B
December 6, 2012 at 4:43 am
Thannks Both of you
The above query works :
Declare @Param_Start date,
@Param_End date
select @Param_Start='11/05/2012',
@Param_End='11/11/2012'
;with cte1(date1)
as
(
select convert(date,@Param_Start)
union all
select DATEADD(week,1,date1) from cte1
where date1<=@Param_End
)
select * from cte1
But my concern is say I have the start date as 11/05/2012 and end date as 11/11/2012, the results are
11/05/2012
11/12/2012 -- Here 11/11/2012 is considered to be in the subsequent Monday week... But I wanted 11/11/2012 's start of the week as 11/05/2012... I wanted the week to be Like commencing Monday till Sunday as one enitre week.
any help ?
thanks
December 6, 2012 at 4:51 am
I provided you with a working solution earlier. Have you tied it?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
December 6, 2012 at 5:03 am
check this ...
Declare @Param_Start date,
@Param_End date
select @Param_Start=DATEADD(DD, 2-DATEPART(DW, '2012-11-01'), '2012-11-01'),
@Param_End='11/11/2012'
;with cte1(date1)
as
(
select convert(date,@Param_Start)
union all
select DATEADD(week,1,date1) from cte1
where date1<=@Param_End
)
select * from cte1
December 6, 2012 at 5:57 am
Ya , I tried it out Thanks!
But I feel this will not be an optimal solution satisfying the requirement. Better option would be a date dimension table. Thanks anywyas!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply