Getting next Mon., Tues, etc. - how do I? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

Transact-SQL (2000)

Getting next Mon., Tues, etc. - how do I?

Author

Topic

gammax500
Starting Member

9 Posts

Posted - 2003-04-18 : 20:29:45

Given a date @startingdate:

How do I get the first monday EQUAL TO or following @startingdate,
the first tuesday equal to or following @startingdate, etc?

I know I can get the day of the week to which @startingdate belongs like:

declare @thisday datetime
set @thisday = {d '2000-01-12'}

SELECT
dateadd(dd, 1 - datepart(dw, @thisday), @thisday) AS SundayDate,
dateadd(dd, 2 - datepart(dw, @thisday), @thisday) AS MondayDate,
dateadd(dd, 3 - datepart(dw, @thisday), @thisday) AS TuesdayDate,
dateadd(dd, 4 - datepart(dw, @thisday),@thisday) AS WednesdayDate
...

But if @thisday is a Wednesday, then I would like the sunday, monday, tuesday following @thisday, and the others to stay what is returned above.

Is this hard?

TIA,
Ann

Edited by - gammax500 on 04/18/2003 20:34:43

Edited by - gammax500 on 04/18/2003 20:57:24

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-18 : 20:54:23

Well this works, but hopefully someone else has a better solution. This solution gives you the first Monday equal to or following the current date.

DECLARE @weekdate INT
DECLARE @startingdate DATETIME

SET @startingdate = GETDATE()

SELECT @weekday = DATEPART(WEEKDAY, @startingdate)

SELECT 
  CASE 
     WHEN @weekday = 1 THEN DATEADD(DAY, 1, @startingdate)
     WHEN @weekday = 2 THEN DATEADD(DAY, 7, @startingdate)
     WHEN @weekday = 3 THEN DATEADD(DAY, 6, @startingdate)
     WHEN @weekday = 4 THEN DATEADD(DAY, 5, @startingdate)
     WHEN @weekday = 5 THEN DATEADD(DAY, 4, @startingdate)
     WHEN @weekday = 6 THEN DATEADD(DAY, 3, @startingdate)
     WHEN @weekday = 7 THEN DATEADD(DAY, 2, @startingdate)
  END

Tara

Edited by - tduggan on 04/18/2003 20:55:35

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-18 : 20:59:43

use dateadd and datepart(dw

Something like
declare @i int
select @i = @@datefirst
set datefirst 2
select dateadd(dd,7 - datepart(dw,getdate()),getdate())
set datefirst @i

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-18 : 21:10:38

if you are sure about the datefirst on your systems

dateadd(dd,(9-datepart(dw,@d))%7,@d)

will do it change the 9 depending on the day you want.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-18 : 21:16:12

DECLARE @Monday AS SMALLINT
DECLARE @Tuesday AS SMALLINT
DECLARE @Wednesday AS SMALLINT
DECLARE @Thursday AS SMALLINT
DECLARE @Friday As SMALLINT
DECLARE @Saturday AS SMALLINT
DECLARE @Sunday AS SMALLINT
DECLARE @StartingDate AS DATETIME
DECLARE @NextDay AS SMALLINT
DECLARE @i AS SMALLINT

SET @Monday = 2
SET @Tuesday = 3
SET @Wednesday = 4
SET @Thursday = 5
SET @Friday = 6
SET @Saturday = 7
SET @Sunday = 1

SET @StartingDate = '20030421'

SELECT @i = @@datefirst

SET DATEFIRST @Monday

SELECT @StartingDate + (7-datepart(dw,@StartingDate))

SET DATEFIRST @i

might as well post after all this

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-18 : 21:17:27

nice one NR
So simple it hurts

Edited by - ValterBorges on 04/18/2003 21:25:41

gammax500
Starting Member

9 Posts

Posted - 2003-04-18 : 21:19:54

Thank you ALL for your very informative posts. I especially enjoyed the post by nr.

Thanks again,
Ann

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources