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.
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 datesfor example week number : 1month number : 1year: 2013the output has to be 01/01/201301/02/201301/03/201301/04/201301/05/201301/06/201301/07/2013as like this based on week number i want to get the dates falls in the week.below query give dates based on monthdeclare @month int, @year intset @month = 8set @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] |
 |
|
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. |
 |
|
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] |
 |
|
sqllover
Constraint Violating Yak Guru
338 Posts |
Posted - 2013-08-29 : 22:12:01
|
week : 28month : 7year : 2013dates falls on the 28 week of 2013 JulyResult: 07/07/201307/08/201307/09/201307/10/201307/11/201307/12/201307/13/2013can you please now |
 |
|
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 WeekFrom your post on 08/29/2013 : 22:12:01, it seems like your week starts on SunBut 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] |
 |
|
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 |
 |
|
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] |
 |
|
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. |
 |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-08-29 : 23:09:20
|
[code]declare @year int = 2013, @week int = 27declare @dte dateselect @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)) dWHERE [DATE] >= dateadd(year, @year - 1900, 0)AND [DATE] <= dateadd(year, @year - 1900 + 1, -1)[/code] KH[spoiler]Time is always against us[/spoiler] |
 |
|
sqllover
Constraint Violating Yak Guru
338 Posts |
Posted - 2013-08-30 : 07:48:08
|
works perfect. thank you khtan..... |
 |
|
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 dateselect @dte = dateadd(week, @week - 1, dateadd(year, @year - 1900, 0));BEGINSELECT STUFF(cnvDate, 1, 1, '') as Output FROM(SELECT ',[' + convert(varchar, DATE, 101) + ']' AS cnvDateFROM( 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 |
 |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-08-30 : 21:43:16
|
[code]declare @year int = 2013, @week int = 27DECLARE @dte DATESELECT @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] |
 |
|
sqllover
Constraint Violating Yak Guru
338 Posts |
Posted - 2013-08-30 : 22:13:29
|
Awesome. Thanks a lot Gentle Man!!!! |
 |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-08-31 : 04:05:12
|
welcome KH[spoiler]Time is always against us[/spoiler] |
 |
|
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 = 2SET 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 DateFROM @DatesWHERE WeekNumber = @WeekNumber ORDER BY Date;veeranjaneyulu |
 |
|
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. |
 |
|
VeeranjaneyuluAnnapureddy
Posting Yak Master
169 Posts |
Posted - 2013-09-03 : 06:56:48
|
welcomeveeranjaneyulu |
 |
|
|
|
|
|
|