How to get week of a mont with input parameter date.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » How to get week of a mont with input...

13 posts, Page 1 of 2

1 2 »»

How to get week of a mont with input parameter date.

Rate Topic

Display Mode

Topic Options

Author

Message

xRafo

Posted Tuesday, September 11, 2012 1:45 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 4:41 PM
Points: 49, Visits: 391

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

GSquared

Posted Tuesday, September 11, 2012 2:04 PM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 15,442, Visits: 9,571

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

Lynn Pettis

Posted Tuesday, September 11, 2012 2:09 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:36 PM
Points: 21,615, Visits: 27,446

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

GSquared

Posted Tuesday, September 11, 2012 2:14 PM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 15,442, Visits: 9,571

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

Lynn Pettis

Posted Tuesday, September 11, 2012 2:17 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:36 PM
Points: 21,615, Visits: 27,446

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.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1357694

Lynn Pettis

Posted Tuesday, September 11, 2012 2:19 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:36 PM
Points: 21,615, Visits: 27,446

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.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1357698

Phil Parkin

Posted Wednesday, September 12, 2012 1:48 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 4,240, Visits: 9,487

Lynn Pettis (9/11/2012)

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1357825

GSquared

Posted Wednesday, September 12, 2012 6:10 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 15,442, Visits: 9,571

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

Luis Cazares

Posted Wednesday, September 12, 2012 7:10 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:52 PM
Points: 960, Visits: 1,921

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.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1357957

Lynn Pettis

Posted Wednesday, September 12, 2012 7:34 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:36 PM
Points: 21,615, Visits: 27,446

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.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1357968

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions