September 11, 2012 at 1:45 pm
Hi,
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/09
any help would be useful.
Thanks.
Pd. Sorry for my bad engl.
____________________________________________________________________________
Rafo*
September 11, 2012 at 2:04 pm
Easy enough:
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;
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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 11, 2012 at 2:09 pm
Here is a bit of code that returns what you are looking for:
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;
September 11, 2012 at 2:14 pm
Lynn Pettis (9/11/2012)
Here is a bit of code that returns what you are looking for:
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;
It's the SQL 2005 forum, Lynn. He might not have access to TVFs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 11, 2012 at 2:17 pm
GSquared (9/11/2012)
Lynn Pettis (9/11/2012)
Here is a bit of code that returns what you are looking for:
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;
It's the SQL 2005 forum, Lynn. He might not have access to TVFs.
SQL Server 2005 has table valued functions, I'm lost.
September 11, 2012 at 2:19 pm
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.
September 12, 2012 at 1:48 am
Lynn Pettis (9/11/2012)
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.
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)?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 12, 2012 at 6:10 am
Lynn Pettis (9/11/2012)
GSquared (9/11/2012)
Lynn Pettis (9/11/2012)
Here is a bit of code that returns what you are looking for:
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;
It's the SQL 2005 forum, Lynn. He might not have access to TVFs.
SQL Server 2005 has table valued functions, I'm lost.
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: http://technet.microsoft.com/en-us/library/cc721270(v=SQL.100).aspx).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 12, 2012 at 7:10 am
In 2005 there are TVF (Table Valued Functions)
There are not Table Value Constructors.
Is that what you meant?
It can be fixed by
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;
September 12, 2012 at 7:34 am
Luis Cazares (9/12/2012)
In 2005 there are TVF (Table Valued Functions)There are not Table Value Constructors.
Is that what you meant?
It can be fixed by
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;
Now that I will agree with and can correct.
Thanks, Luis.
September 12, 2012 at 7:39 am
Lynn Pettis (9/11/2012)
Here is a bit of code that returns what you are looking for:
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 rewritten for SQL Server 2005:
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;
September 12, 2012 at 4:08 pm
xRafo (9/11/2012)
Hi,i have a question,
How to get First week of a actual month?
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...
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/09
any help would be useful.
If that's what you really want, then the other posters have already posted some dandy ideas on how to do that.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 12, 2012 at 8:25 pm
Lynn Pettis (9/12/2012)
Lynn Pettis (9/11/2012)
Here is a bit of code that returns what you are looking for:
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 rewritten for SQL Server 2005:
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;
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.
SELECT DATEADD(dd,DATEDIFF(dd,-1,GETDATE())/7*7+(t.N),-1)
FROM dbo.Tally t
WHERE t.N BETWEEN 0 AND 6
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply