Find first day of a week/month

Question

Post reply

Find first day of a week/month

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Here's an alternative (quite obscure) solution to a similar problem:

http://www.sqlservercentral.com/Forums/FindPost1011841.aspx

Peso (Peter Larsson) is incredibly clever and his code is not for everyone...

-- Gianluca Sartori

drew.allen SSC Guru Points: 76999 More actions · Answer 2

SQLRNNR (9/28/2011)
It appears there is an issue with how SQL handles dates that land on Sunday.

Actually the problem lies with this piece of code:

DATEADD(wk, DATEDIFF(wk, 0, GetDate()), 0)

Similar code works well with other date parts, but there are problems with the week date part. The reason for this is that the formula depends on the fact that date 0 ('1900-01-01') falls on the beginning of the date part. That is, date 0 is the first day of the year, first hour of the day, first minute of the hour, etc. However, this requirement is not necessarily met for days of the week. Date 0 falls on a Monday and whether Monday is the first day of the week depends on the value of the datefirst setting. The above code needs to be adjusted to reflect the value of the datefirst setting. So, for weeks beginning Sunday, I use the following:

DATEADD(wk, DATEDIFF(wk, -1, GetDate()), -1)

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

drew.allen SSC Guru Points: 76999 More actions · Answer 3

Kingston Dhasian (9/28/2011)
This should work for you then..
DECLARE @from_date DATETIME
SET @from_date = '11-Sep-2011'
SELECT CASE
WHEN DATEADD( MONTH, DATEDIFF( MONTH, 0, @from_date ), 0 ) > DATEADD( WEEK, DATEDIFF( WEEK, 0, @from_date - 1 ), 0 )
THEN DATEADD( MONTH, DATEDIFF( MONTH, 0, @from_date ), 0 )
ELSE DATEADD( WEEK, DATEDIFF( WEEK, 0, @from_date - 1 ), 0 )
END

The condition can be simplified to remove half of the function calls and remove all of the implicit conversion from int to datetime.

DECLARE @from_date DATETIME

SET @from_date = '11-Sep-2011'

SELECT CASE

WHEN DATEPART(d, @from_date ) < DATEPART(dw, @from_date)

THEN DATEADD( MONTH, DATEDIFF( MONTH, 0, @from_date ), 0 )

ELSE DATEADD( WEEK, DATEDIFF( WEEK, 0, @from_date - 1 ), 0 )

END

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

drew.allen (9/28/2011)
SQLRNNR (9/28/2011)
It appears there is an issue with how SQL handles dates that land on Sunday.
Actually the problem lies with this piece of code:
DATEADD(wk, DATEDIFF(wk, 0, GetDate()), 0)
Similar code works well with other date parts, but there are problems with the week date part. The reason for this is that the formula depends on the fact that date 0 ('1900-01-01') falls on the beginning of the date part. That is, date 0 is the first day of the year, first hour of the day, first minute of the hour, etc. However, this requirement is not necessarily met for days of the week. Date 0 falls on a Monday and whether Monday is the first day of the week depends on the value of the datefirst setting. The above code needs to be adjusted to reflect the value of the datefirst setting. So, for weeks beginning Sunday, I use the following:
DATEADD(wk, DATEDIFF(wk, -1, GetDate()), -1)
Drew

The problem is not for weeks beginning Sunday, but for when the date is Sunday and the week begins on monday. If you put in a Sunday related date, it jumps to the monday start date of the upcoming week rather than the prior Monday.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 5

codebyo (9/27/2011)
Hmm. What do you mean? September 1st is Thursday, not the first day of the week.
Day of week would be like this:
SELECT CASE DATEPART(WEEKDAY, '20110901')
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END;
Best regards,

It's a lot simpler to use what's built in for this type of thing... and you get "language flexibility" to boot not to mention no longer being dependent on the correct setting of DATEFIRST...

SELECT DATENAME(WEEKDAY, '20110901')

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 6

Jeff Moden (10/2/2011)
codebyo (9/27/2011)
Hmm. What do you mean? September 1st is Thursday, not the first day of the week.
Day of week would be like this:
SELECT CASE DATEPART(WEEKDAY, '20110901')
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END;
Best regards,
It's a lot simpler to use what's built in for this type of thing... and you get "language flexibility" to boot not to mention no longer being dependent on the correct setting of DATEFIRST...
SELECT DATENAME(WEEKDAY, '20110901')

Yes, you're right. I forgot there was a builtin function for that. Thanks for reminding me. 🙂

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA