InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 12:07:40 GMT20http://www.sqlservercentral.com/Forums/Topic1357675-338-1.aspx[quote][b]Lynn Pettis (9/12/2012)[/b][hr][quote][b]Lynn Pettis (9/11/2012)[/b][hr]Here is a bit of code that returns what you are looking for:[code="sql"]declare @TestDate date = '2012-09-11';with SevenRows(n) as (select row_number() over (order by (select null)) - 1 from (values (1),(1),(1),(1),(1),(1),(1))dt(n))select dateadd(dd, n,dateadd(wk,datediff(wk,0,dateadd(dd,-1,@TestDate)),0)) from SevenRows;[/code][/quote]Code rewritten for SQL Server 2005:[code="sql"]declare @TestDate date = '2012-09-11';with SevenRows(n) as (select row_number() over (order by (select null)) - 1 from (select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1)dt(n))select dateadd(dd, n,dateadd(wk,datediff(wk,0,dateadd(dd,-1,@TestDate)),0)) from SevenRows;[/code][/quote]I have a general distrust for the "wk" datepart even though it doesn't seem to matter matter here. With that thought in mind, here's a bit of code that uses a zero based Tally Table. It can be easily modified to handle a unit based Tally Table if needed. If nothing else, it makes for some really simple code.[code="sql"] SELECT DATEADD(dd,DATEDIFF(dd,-1,GETDATE())/7*7+(t.N),-1) FROM dbo.Tally t WHERE t.N BETWEEN 0 AND 6[/code]Wed, 12 Sep 2012 20:25:27 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1357675-338-1.aspx[quote][b]xRafo (9/11/2012)[/b][hr]Hi,i have a question,How to get First week of a actual month?[/quote]Please define what you mean by "first week of the month". For example, is it the actual first 7 days of the month regardless of which day of the week it starts on? Is it the week starting on Sunday (for example) that contains the first of the month even if it starts on a Saturday? Is it the week of the month starting on Sunday (for example) that contains at least the first 4 calendar days of the month (like ISO).What would you define as the first week of September 2012 and why (for example???)Now... all of that appears to be contrary to the rest of your request...[quote]In my procedure i will send a parameter date: Example input -> today()output <- 10/09 | 11/09 | 12/09 | 13/09 | 14/09 | 15/09 | 16/09any help would be useful.[/quote]If that's what you really want, then the other posters have already posted some dandy ideas on how to do that.Wed, 12 Sep 2012 16:08:35 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1357675-338-1.aspx[quote][b]Lynn Pettis (9/11/2012)[/b][hr]Here is a bit of code that returns what you are looking for:[code="sql"]declare @TestDate date = '2012-09-11';with SevenRows(n) as (select row_number() over (order by (select null)) - 1 from (values (1),(1),(1),(1),(1),(1),(1))dt(n))select dateadd(dd, n,dateadd(wk,datediff(wk,0,dateadd(dd,-1,@TestDate)),0)) from SevenRows;[/code][/quote]Code rewritten for SQL Server 2005:[code="sql"]declare @TestDate date = '2012-09-11';with SevenRows(n) as (select row_number() over (order by (select null)) - 1 from (select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1)dt(n))select dateadd(dd, n,dateadd(wk,datediff(wk,0,dateadd(dd,-1,@TestDate)),0)) from SevenRows;[/code]Wed, 12 Sep 2012 07:39:30 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1357675-338-1.aspx[quote][b]Luis Cazares (9/12/2012)[/b][hr]In 2005 there are TVF (Table Valued Functions)There are not Table Value Constructors.Is that what you meant?It can be fixed by[code="sql"]declare @TestDate date = '2012-09-12';with SevenRows(n) as ( select row_number() over (order by (select null)) - 1 from (SELECT TOP 7 NULL FROM sys.columns)dt(n))select dateadd(dd, n,dateadd(wk,datediff(wk,0,dateadd(dd,-1,@TestDate)),0)) from SevenRows;[/code][/quote]Now that I will agree with and can correct.Thanks, Luis.Wed, 12 Sep 2012 07:34:43 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1357675-338-1.aspxIn 2005 there are TVF (Table Valued Functions)There are not Table Value Constructors.Is that what you meant?It can be fixed by[code="sql"]declare @TestDate date = '2012-09-12';with SevenRows(n) as ( select row_number() over (order by (select null)) - 1 from (SELECT TOP 7 NULL FROM sys.columns)dt(n))select dateadd(dd, n,dateadd(wk,datediff(wk,0,dateadd(dd,-1,@TestDate)),0)) from SevenRows;[/code]Wed, 12 Sep 2012 07:10:29 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1357675-338-1.aspx[quote][b]Lynn Pettis (9/11/2012)[/b][hr][quote][b]GSquared (9/11/2012)[/b][hr][quote][b]Lynn Pettis (9/11/2012)[/b][hr]Here is a bit of code that returns what you are looking for:[code="sql"]declare @TestDate date = '2012-09-11';with SevenRows(n) as (select row_number() over (order by (select null)) - 1 from (values (1),(1),(1),(1),(1),(1),(1))dt(n))select dateadd(dd, n,dateadd(wk,datediff(wk,0,dateadd(dd,-1,@TestDate)),0)) from SevenRows;[/code][/quote]It's the SQL 2005 forum, Lynn. He might not have access to TVFs.[/quote]SQL Server 2005 has table valued functions, I'm lost.[/quote]No, it doesn't. I just tried it on SQL 2005 Dev Edition, and it didn't work. Plus, per TechNet, it was a new feature in SQL 2008 (ref: [url]http://technet.microsoft.com/en-us/library/cc721270(v=SQL.100).aspx[/url]).Wed, 12 Sep 2012 06:10:04 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1357675-338-1.aspx[quote][b]Lynn Pettis (9/11/2012)[/b][hr]Plus, we only have part of the problem. We should wait for the other shoe to drop and see how this is going to be used in the procedure. Also, he did say a parameter was being passed, the single input value, and wanted 7 values returned.[/quote]Also puzzling (to me at least) is the "First week of a actual month" requirement - whereas the sample data supplied was not the first week in September, by my reckoning. Surely it would be the week commencing 2 or 3 September (depending on which day you choose as the start day)?Wed, 12 Sep 2012 01:48:44 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1357675-338-1.aspxPlus, we only have part of the problem. We should wait for the other shoe to drop and see how this is going to be used in the procedure. Also, he did say a parameter was being passed, the single input value, and wanted 7 values returned.Tue, 11 Sep 2012 14:19:17 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1357675-338-1.aspx[quote][b]GSquared (9/11/2012)[/b][hr][quote][b]Lynn Pettis (9/11/2012)[/b][hr]Here is a bit of code that returns what you are looking for:[code="sql"]declare @TestDate date = '2012-09-11';with SevenRows(n) as (select row_number() over (order by (select null)) - 1 from (values (1),(1),(1),(1),(1),(1),(1))dt(n))select dateadd(dd, n,dateadd(wk,datediff(wk,0,dateadd(dd,-1,@TestDate)),0)) from SevenRows;[/code][/quote]It's the SQL 2005 forum, Lynn. He might not have access to TVFs.[/quote]SQL Server 2005 has table valued functions, I'm lost.Tue, 11 Sep 2012 14:17:00 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1357675-338-1.aspx[quote][b]Lynn Pettis (9/11/2012)[/b][hr]Here is a bit of code that returns what you are looking for:[code="sql"]declare @TestDate date = '2012-09-11';with SevenRows(n) as (select row_number() over (order by (select null)) - 1 from (values (1),(1),(1),(1),(1),(1),(1))dt(n))select dateadd(dd, n,dateadd(wk,datediff(wk,0,dateadd(dd,-1,@TestDate)),0)) from SevenRows;[/code][/quote]It's the SQL 2005 forum, Lynn. He might not have access to TVFs.Tue, 11 Sep 2012 14:14:41 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1357675-338-1.aspxHere is a bit of code that returns what you are looking for:[code="sql"]declare @TestDate date = '2012-09-11';with SevenRows(n) as (select row_number() over (order by (select null)) - 1 from (values (1),(1),(1),(1),(1),(1),(1))dt(n))select dateadd(dd, n,dateadd(wk,datediff(wk,0,dateadd(dd,-1,@TestDate)),0)) from SevenRows;[/code]Tue, 11 Sep 2012 14:09:54 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1357675-338-1.aspxEasy enough:[code="sql"]DECLARE @InputDate DATE = GETDATE();SELECT DATEADD(month, DATEDIFF(MONTH, 0, @InputDate), 0) AS FirstDayOfMonth, DATEADD(week, DATEDIFF(week, 0, DATEADD(month, DATEDIFF(MONTH, 0, @InputDate), 0)), 0) - 1 AS FirstDayOfWeek, DATEADD(week, DATEDIFF(week, 0, DATEADD(month, DATEDIFF(MONTH, 0, @InputDate), 0)), 0) AS SecondDayOfWeek, DATEADD(week, DATEDIFF(week, 0, DATEADD(month, DATEDIFF(MONTH, 0, @InputDate), 0)), 0) + 1 AS ThirdDayOfWeek, DATEADD(week, DATEDIFF(week, 0, DATEADD(month, DATEDIFF(MONTH, 0, @InputDate), 0)), 0) + 2 AS FourthDayOfWeek, DATEADD(week, DATEDIFF(week, 0, DATEADD(month, DATEDIFF(MONTH, 0, @InputDate), 0)), 0) + 3 AS FifthDayOfWeek, DATEADD(week, DATEDIFF(week, 0, DATEADD(month, DATEDIFF(MONTH, 0, @InputDate), 0)), 0) + 4 AS SixthDayOfWeek, DATEADD(week, DATEDIFF(week, 0, DATEADD(month, DATEDIFF(MONTH, 0, @InputDate), 0)), 0) + 5 AS SeventhDayOfWeek;[/code]The nested DateAdd, DateDiff method allows you to get the first X (time-unit) of any given DateTime value. If you use Days as your unit, you get the very beginning of the day (midnight at the end of the prior day, to be precise). If you use Weeks as your unit, you get the first day of the week. And so on.This one has to be done in two stages:First, get the first day of the month. I put that in its own column, just so you could see how it's done. You can remove that column from the query if you don't want it.Second, get the first day of the week that the first day of the month is in. So it nests the month calculation inside a week calculation. The math at the end of each row is based on Monday being the first day of the week on the server I ran this on. You'll need to confirm that and may need to change the "-1", "+1" through "+5" if the first day of the week is defined as Sunday or whatever on your server.Tue, 11 Sep 2012 14:04:34 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1357675-338-1.aspxHi,i have a question,How to get First week of a actual month?In my procedure i will send a parameter date: Example input -> today()output <- 10/09 | 11/09 | 12/09 | 13/09 | 14/09 | 15/09 | 16/09any help would be useful.Thanks.Pd. Sorry for my bad engl.Tue, 11 Sep 2012 13:45:54 GMTxRafo