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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 get dates from week number month and year

Author  Topic 

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-08-29 : 21:24:35
Hi I have procedure which has date number, month number and year as input. based on the above three parameters i wanted to get the week dates

for example

week number : 1
month number : 1
year: 2013

the output has to be

01/01/2013
01/02/2013
01/03/2013
01/04/2013
01/05/2013
01/06/2013
01/07/2013

as like this based on week number i want to get the dates falls in the week.

below query give dates based on month



declare @month int, @year int

set @month = 8

set @year = 2013




select CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) + Number

FROM master..spt_values WHERE type = 'P' AND

(CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) + Number )
<
DATEADD(mm,1,CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) )



But i am not sure how can i tune this query to make it work for week based.

Can anyone please show me some sample query.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-08-29 : 21:32:59
what is your definition of week / month ?
week 1, month 1 is the first 7 days of January ?
week 4, month 2 is ?


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

Go to Top of Page

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-08-29 : 21:44:37
Hi Khtan,

Thanks for your reply and my requirement is based on the week number i wanted to get all the dates falls on the week.i will be passing week number, month number and year from my application to stored procedure.

Please let me know if my requirement is clear to you.

for ex : if i send 28 as week number, 8 as month and 2013 as year, i will be expecting dates falls on the 28th week.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-08-29 : 22:00:23
quote:
for ex : if i send 28 as week number, 8 as month and 2013 as year, i will be expecting dates falls on the 28th week.

and what is the result for the above ?


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

Go to Top of Page

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-08-29 : 22:12:01
week : 28
month : 7
year : 2013

dates falls on the 28 week of 2013 July

Result:

07/07/2013
07/08/2013
07/09/2013
07/10/2013
07/11/2013
07/12/2013
07/13/2013

can you please now
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-08-29 : 22:25:14
What is the role of "month : 7" here ? it looks like the dates you want is not related to the month but only to the Week

From your post on 08/29/2013 : 22:12:01, it seems like your week starts on Sun

But in your first post, the example you have shown is Week 1 gives dates 01/01/2013 to 01/07/2013. Is there a mistake there ?


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

Go to Top of Page

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-08-29 : 22:28:58
Sorry for the mistake. You are correct. month is not required.

the output should be based on week and year. could you please help me by providing sample query
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-08-29 : 22:35:53
how about my question on week 1 ?


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

Go to Top of Page

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-08-29 : 22:39:15
Hope my last thread clears the requirement. please ignore mistakes on first thread. repeating the requirement again, i am trying to list the dates falls on the week of the year. here week and year will be input to the query. so based on the week number and year we will have to display all the dates falls on the week.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-08-29 : 23:09:20
[code]declare @year int = 2013,
@week int = 27

declare @dte date

select @dte = dateadd(week, @week - 1, dateadd(year, @year - 1900, 0))

SELECT *
FROM
(
SELECT [DATE] = DATEADD(DAY, n, dateadd(day, (datediff(day, '17530107', @dte) / 7) * 7, '17530107'))
FROM (
VALUES (0), (1), (2), (3), (4), (5), (6)
) num (n)
) d
WHERE [DATE] >= dateadd(year, @year - 1900, 0)
AND [DATE] <= dateadd(year, @year - 1900 + 1, -1)
[/code]


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

Go to Top of Page

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-08-30 : 07:48:08
works perfect. thank you khtan.....
Go to Top of Page

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-08-30 : 12:12:49
Hi Khtan,

How to make the results as comma separated. to appear as single row.

Below on i tried. seems not working.

declare @year int = 2013,
@week int = 2


declare @dte date

select @dte = dateadd(week, @week - 1, dateadd(year, @year - 1900, 0));
BEGIN
SELECT
STUFF(cnvDate, 1, 1, '') as Output
FROM(
SELECT ',[' + convert(varchar, DATE, 101) + ']' AS cnvDate
FROM
(
SELECT [DATE] = DATEADD(DAY, n, dateadd(day, (datediff(day, '17530107', @dte) / 7) * 7, '17530107'))
FROM (
VALUES (0), (1), (2), (3), (4), (5), (6)) num (n)
) d WHERE [DATE] >= dateadd(year, @year - 1900, 0)
AND [DATE] <= dateadd(year, @year - 1900 + 1, -1))

END

could you please help me
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-08-30 : 21:43:16
[code]
declare @year int = 2013,
@week int = 27

DECLARE @dte DATE

SELECT @dte = dateadd(WEEK, @week - 1, DATEADD(YEAR, @year - 1900, 0))

SELECT STUFF(
(
SELECT ',[' + CONVERT(VARCHAR(10), [DATE], 101) + ']'
FROM
(
SELECT [DATE] = DATEADD(DAY, n, DATEADD(DAY, (DATEDIFF(DAY, '17530107', @dte) / 7) * 7, '17530107'))
FROM (
VALUES (0), (1), (2), (3), (4), (5), (6)
) num (n)
) d
WHERE [DATE] >= DATEADD(YEAR, @year - 1900, 0)
AND [DATE] <= DATEADD(YEAR, @year - 1900 + 1, -1)
for xml path('')
), 1, 1, '')[/code]


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

Go to Top of Page

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-08-30 : 22:13:29
Awesome. Thanks a lot Gentle Man!!!!
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-08-31 : 04:05:12
welcome


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

Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-08-31 : 08:12:40
DECLARE @Date DATE='2013-01-01',@StartOfWeek TINYINT=1;
DECLARE @Month TINYINT= MONTH(@Date);
DECLARE @Dates TABLE(DATE DATE,weeknumber AS DATEPART(WEEK,DATE))
DECLARE @WeekNumber INT = 2

SET DATEFIRST @StartOfWeek; -- Start week on Sunday

WHILE (MONTH(@Date) = @Month)
BEGIN
INSERT @Dates (DATE) SELECT @Date;
SET @Date = DATEADD(DD,1,@Date);
END

SELECT Date as Date
FROM @Dates
WHERE WeekNumber = @WeekNumber
ORDER BY Date;


veeranjaneyulu
Go to Top of Page

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-09-03 : 06:36:40
Hi Veera,

Khtan's reply is the perfect solution for my requirement. Thanks for your consideration on my thread and welcome to the knowledge sharing world.
Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-09-03 : 06:56:48
welcome

veeranjaneyulu
Go to Top of Page
   

- Advertisement -