Post History

## Quick answer

```sql
(DATE_PART(dayofweek, my_datetime) + 6) % 7
```

## Slow answer

For avoidance of doubt, here is what I believe you currently have (assuming an input called `my_datetime`):

```sql
DATE_PART(dayofweek, my_datetime)
```

Or its abbreviated equivalent:

```sql
DATE_PART(dow, my_datetime)
```

This returns 0 if `my_datetime` is a Sunday.

You want a number from 0 to 6, representing Monday to Sunday. Instead you have a number from 0 to 6, representing Sunday to Saturday. If there doesn't turn out to be a straightforward way to access the format you want directly, there are 2 possible approaches that will probably be simpler (to write and to read) than using a `CASE` statement.

### Modify the output
If you add 6 to the output, for a Sunday it will now be 6 instead of 0, as required. However, for all the other days of the week it will now be greater than 6. This can be fixed by reducing the answer modulo 7 (that is, taking the remainder after dividing by 7). Now every day of the week has the required number.

```sql
(DATE_PART(dayofweek, my_datetime) + 6) % 7
```

You may find it more intuitive to think of subtracting 1 from the output, rather than adding 6. Personally I avoid negative numbers when using the modulo operator because different languages and different implementations give different results for negative numbers, but they all give consistent results for positive numbers. I include the following in case you find it more readable, but **I recommend against using it unless you are certain it will never be used in a different SQL implementation:**

```sql
(DATE_PART(dayofweek, my_datetime) - 1) % 7
```

*Note that the [documentation for Redshift's MOD operator](https://docs.aws.amazon.com/redshift/latest/dg/r_MOD.html) does not currently specify its behaviour with negative numbers.*

### Modify the input
Instead of modifying the output, you could ask what day of the week it will be in 6 days time, which will always give the correct answer:

```sql
DATE_PART(dayofweek, DATEADD(day, 6, my_datetime))
```

You may find it more intuitive to ask what day it was a day earlier, by subtracting 1 day from the input datetime. Personally I avoid negative numbers where possible as some SQL implementations have subtle bugs, such as breaking when crossing a year boundary with a negative offset. I include the following in case you find it more readable but **please bear in mind that it should only be used in an implementation that is safe for negative numbers:**

```sql
DATE_PART(dayofweek, DATEADD(day, -1, my_datetime))
```