Josh - 1 year ago 76

SQL Question

What I need is a date for the next given day (Monday, Tuesday, Wed...) following today's date.

The user is allowed to select what day following they want and that is stored as an int in a table. "Call me next Tuesday (3)"

`Sunday = 1`

Monday = 2

Tuesday = 3

...

So my table looks like this.

`UserID, NextDayID`

What I have come up with is:

`select dateadd(dd,(7 - datepart(dw,GETDATE()) + NextDayID ) % 7, getdate())`

It seems to work and will return today's date if you ask for the next whatever day today is which I can add a week if needed.

What I am wondering is, is that a good solution or is there something that I'm missing?

Answer Source

1) Your solution uses a non-deterministic function: `datepart(dw...)`

. Because of this aspect, changing `DATEFIRST`

setting will gives different results. For example, you should try:

```
SET DATEFIRST 7;
your solution;
```

and then

```
SET DATEFIRST 1;
your solution;
```

2) Following solution is independent of `DATEFIRST`

/`LANGUAGE`

settings:

```
DECLARE @NextDayID INT = 0 -- 0=Mon, 1=Tue, 2 = Wed, ..., 5=Sat, 6=Sun
SELECT DATEADD(DAY, (DATEDIFF(DAY, @NextDayID, GETDATE()) / 7) * 7 + 7, @NextDayID) AS NextDay
```

Result:

```
NextDay
-----------------------
2013-09-23 00:00:00.000
```

This solution is based on following property of `DATETIME`

type:

Day 0 =

`19000101`

= MonDay 1 =

`19000102`

= TueDay 2 =

`19000103`

= Wed

...

Day 5 =

`19000106`

= SatDay 6 =

`19000107`

= Sun

So, converting INT value 0 to DATETIME gives `19000101`

.

If you want to find the next `Wednesday`

then you should start from day 2 (`19000103`

/`Wed`

), compute days between day 2 and current day (`20130921`

; 41534 days), divide by 7 (in order to get number of full weeks; 5933 weeks), multiple by 7 (41531 fays; in order to get the number of days - full weeks between the first `Wednesday`

/`19000103`

and the last `Wednesday`

) and then add 7 days (one week; 41538 days; in order to get following `Wednesday`

). Add this number (41538 days) to the starting date: `19000103`

.

Note: my current date is `20130921`

.

**Edit #1:**

```
DECLARE @NextDayID INT;
SET @NextDayID = 1; -- Next Sunday
SELECT DATEADD(DAY, (DATEDIFF(DAY, ((@NextDayID + 5) % 7), GETDATE()) / 7) * 7 + 7, ((@NextDayID + 5) % 7)) AS NextDay
```

Result:

```
NextDay
-----------------------
2013-09-29 00:00:00.000
```

Note: my current date is `20130923`

.