how to get starting datetime(monday) of the week and ending datetime of the week(sunday)

hi friends,

how to get the date of the first/last day of the week in sql...

can any one help me

Cheers,

raj

Raj,

Do clarify what would be the input.

Do u want to find the the first and last datetime of a week for an Entire Year or a month.

Or do u want to get the First and last day of a week by providing any date that falls in between.

SanDoty

|||

THANX FOR UR REPLY,

I want to get the First and last day of a week by providing CURRENT or anydate that falls in between that week

|||

This provides the beginning of the current week, and the beginning of the next week. In your query, you want rows that are >= CurrentWeek AND < NextWeek.

Declare
@.CurrentWeekStart datetime,
@.NextWeekStart datetime

SELECT
@.CurrentWeekStart = dateadd( wk, datediff( wk, 6, getdate() ), 6 ),
@.NextWeekStart = dateadd( Wk, 1, (dateadd( wk, datediff( wk, 6, getdate() ), 6 )))

SELECT @.CurrentWeekStart, @.NextWeekStart

CurrentWeek NextWeek
2007-04-29 00:00:00.000 2007-05-06 00:00:00.000

SELECT *
FROM MyTable
WHERE MyDateColumn >= @.CurrentWeekStart AND MyDateColumn < @.NextWeekStart

|||thank u Arnie..