Select all records since start month and week

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select all records since start month and week

Author

Topic

yidrasil
Starting Member

21 Posts

Posted - 2012-01-04 : 15:26:52

Folks,

I'm having a few difficulties putting together a a pair of MS SQL queries to retrieve;

Query 1: All records from start of month to today (then tomorrow etc)

Query 2: All records from start of week (week begins Monday inclusive)

I'm probably overthinking this but it's slowed down my app progress development considerably.

Grateful for help

Yid

kfluffie
Posting Yak Master

103 Posts

Posted - 2012-01-04 : 16:30:34

Hi,
SELECT * FROM table
WHERE month(dateCol) = month(GetDate())

SELECT * FROM table
WHERE DATEPART(ww,dateCol) = DATEPART(ww,GetDate())

kfluffie
Posting Yak Master

103 Posts

Posted - 2012-01-04 : 16:31:33

Read this link also as the week might start on Sunday or Monday depending on where you are:
http://msdn.microsoft.com/en-us/library/ms174420.aspx

yidrasil
Starting Member

21 Posts

Posted - 2012-01-04 : 16:48:01

Many, many thanks Kfluffie..that should be just the job and take your point about the week start day.

Thanks again,

Yid

kfluffie
Posting Yak Master

103 Posts

Posted - 2012-01-04 : 17:00:34

Also, you need to check the year. Just add WHERE year(dateCol) = yeat(getDate()) so it only check the month/week for this year.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-04 : 17:03:55

ummmmmmmm...

If you post some DDL...I'm guessing the start of every month and or week is for the month or week that you are in (AND year)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-04 : 17:08:41

Some stuff I played with..I know there's probably something better


DECLARE @Last_Monday	 DateTime
DECLARE @Last_Monday_6AM DateTime
DECLARE @Last_Friday_6PM DateTime
DECLARE @day int; SET @day = 1; -- 1 = Monday, 2 = Tuesday, ect

SELECT	@Last_Monday	  = DATEADD(DAY, DATEDIFF(DAY, @day - 1, GetDate()-DATEPART(dw,Getdate())) / 7 * 7, @day - 1)
SELECT	@Last_Monday_6AM  = DATEADD(hh,6,@Last_Monday)
SELECT	@Last_Friday_6PM  = DATEADD(dd,4,DATEADD(hh,18,@Last_Monday))

SELECT @Last_Monday, @Last_Monday_6AM, @Last_Friday_6PM

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

yidrasil
Starting Member

21 Posts

Posted - 2012-01-05 : 10:39:35

Many thanks Brett..apols for not providing more detail and DDL on this one.

Thanks for your solution also...am looking at both yours and kfluffie for performance differences. Also Kfluffies point about the year is a good one as the data in question can historically cover several years.

The routines are for a performance statistics dashboard that will read selected records from the start of each month and week in that month for the given year. So the count resets each month to zero and increments. A similar cycle happens for the data on a weekly basis.

Thanks for all your help guys...much appreciated.

Yid

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-05 : 10:54:57

Just use BETWEEN with mine

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources