| Author |
 Topic  |
|
Ness
Starting Member
1 Posts |
 Posted - 07/30/2010 : 08:17:41
|
Not sure if this will help anyone but I use this to get the last day of the week of a given date:
--Replace the Getdate() with your date
SET DATEFIRST 1
select
CASE datepart(weekday,getdate())
WHEN 1 THEN CONVERT(VARCHAR(20),(GETDATE()+6),103)
WHEN 2 THEN CONVERT(VARCHAR(20),(GETDATE()+5),103)
WHEN 3 THEN CONVERT(VARCHAR(20),(GETDATE()+4),103)
WHEN 4 THEN CONVERT(VARCHAR(20),(GETDATE()+3),103)
WHEN 5 THEN CONVERT(VARCHAR(20),(GETDATE()+2),103)
WHEN 6 THEN CONVERT(VARCHAR(20),(GETDATE()+1),103)
WHEN 7 THEN CONVERT(VARCHAR(20),(GETDATE()),103)
END [End Date] |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 07/30/2010 : 14:57:16
|
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 6)
N 56°04'39.26"
E 12°55'05.63" |
Edited by - SwePeso on 07/30/2010 14:58:36 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 05/06/2011 : 09:21:21
|
quote:
Originally posted by jesuraja
SELECT DATEADD(DAY,-(DATEPART(WEEKDAY,GETDATE()))+@@DATEFIRST,GETDATE()) AS WeekStartDate,
DATEADD(DAY,7-(DATEPART(WEEKDAY,GETDATE())),GETDATE()) AS WeekEndDate
Output
======
WeekStartDate WeekEndDate
----------------------- -----------------------
2011-05-07 16:30:41.310 2011-05-07 16:30:41.310
Jesuraja I
Your week starts and ends on the same date.
If you're going to bump an old thread, at least post a correct answer.
CODO ERGO SUM |
|
|
|
jesuraja
Starting Member
India
3 Posts |
Posted - 05/07/2011 : 05:16:26
|
SET DATEFIRST 1 -- Set First Weekday as Monday
DECLARE @Date DATETIME
SET @Date = GETDATE()
-- First Weekday is 'Monday' and last Weekday is 'Sunday'
SELECT @Date AS ActualDate,
DATEADD(DAY,@@DATEFIRST-DATEPART(WEEKDAY,@Date),@Date) AS WeekStartDate,
DATEADD(DAY,7-DATEPART(WEEKDAY,@Date),@Date) AS WeekEndDate
Output
======
ActualDate WeekStartDate WeekEndDate
----------------------- ----------------------- -----------------------
2011-05-07 11:43:38.890 2011-05-02 11:43:38.890 2011-05-08 11:43:38.890
Jesuraja I |
Edited by - jesuraja on 05/07/2011 07:51:18 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 05/08/2011 : 04:43:04
|
Set datefirst will force a recompile when your query runs.
Are you sure you want that?
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
Reporter
Starting Member
Russia
48 Posts |
Posted - 09/30/2011 : 02:13:18
|
sql server 2008
select datename(weekday,'5/13/2005') |
|
|
|
Fromper
Starting Member
USA
30 Posts |
Posted - 02/02/2012 : 15:22:07
|
| I just wanted to say thanks for the useful thread. I found what I needed here. |
|
|
|
Topic |
|
|
|
get first and last day of a week
