Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to get week of a mont with input parameter date. Expand / Collapse
Author
Message
Posted Tuesday, September 11, 2012 1:45 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 4, 2013 10:17 AM
Points: 57, Visits: 419
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*
Post #1357675
Posted Tuesday, September 11, 2012 2:04 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1357683
Posted Tuesday, September 11, 2012 2:09 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:54 PM
Points: 23,227, Visits: 31,921
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;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1357687
Posted Tuesday, September 11, 2012 2:14 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1357692
Posted Tuesday, September 11, 2012 2:17 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:54 PM
Points: 23,227, Visits: 31,921
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1357694
Posted Tuesday, September 11, 2012 2:19 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:54 PM
Points: 23,227, Visits: 31,921
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1357698
Posted Wednesday, September 12, 2012 1:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 5,030, Visits: 11,763
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)?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1357825
Posted Wednesday, September 12, 2012 6:10 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1357926
Posted Wednesday, September 12, 2012 7:10 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 3,499, Visits: 7,549
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;




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1357957
Posted Wednesday, September 12, 2012 7:34 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:54 PM
Points: 23,227, Visits: 31,921
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1357968
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse