SQL Server – Getting Date of Specific Day from Last Week

datetimesql servert-sql

In learning how to get the date of a day of the week from the previous week (e.g. the date of Monday from the previous week), I found the following two expressions coming up commonly, where you change the very last number (e.g. 0=Monday) depending on which day you want:

SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0)

SELECT DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 0)

Both seem to return the correct Date 2017-05-29 when I run it today (2017-06-17).

My question is – how does this work? It seems to be getting a count of weeks since 6 or 7 weeks after 1900-01-01? And why does it work with both a 6 and 7?

Also, are there edge cases one would need to be aware of where it might not give the expected results?

Best Answer

This takes advantage of the fact that 1900-01-01 is a Monday. So adding n number of weeks will also be a Monday.

Here are the datetime values that are being represented by the integers in that expression:

dateadd(week, datediff(week, 7 /*'19000108'*/, getdate()), 0 /*'19000101'*/)

Adding the number of weeks since 1900-01-08 (the week after the first Monday) to 1900-01-01 returns one week less than the current week, i.e. Monday of last week.

These three are equivalent:

select dateadd(week, datediff(week, 7 , getdate()), 0)
select dateadd(week, datediff(week, '19000108', getdate()), '19000101')
select dateadd(week, datediff(week, 0, getdate())-1, 0)

rextester demo: http://rextester.com/BQX59903

Firstly...

Separating Datime/Time into a Date dimension and a Time dimension is definitely the way to go.

To manage multiple time zones you need to duplicate the DateKey and the TimeKey so that you have the following:

LocalDateKey
LocalTimeKey
UtcDateKey
UtcTimeKey

You say...

The problem I am having with all that is that 11:00 PM on Tuesday, December 31, 2013 in UTC is Wednesday, January 1st, 2014 in all time zones that are after UTC+2.

By having the 4 columns I've listed above you, will be able to join the fact table to the Date and/or Time dimension Using Table Aliases (in Kimball terminology these aliased dimension tables are known as "Role Playing Dimensions"), so you would have something like the following:

/*
    Assumes the following:
        - [DateLongName] has the format of this example "Tuesday, December 31, 2013"
        - [TimeShortName] has the format of this example "11:00 PM"
        - Both [DateLongName] & [TimeShortName] are strings
*/
select
    -- Returns a string matching this example  "11:00 PM Tuesday, December 31, 2013"
    localTime.TimeShortName + ' ' + localDate.DateLongName
    ,utcTime.TimeShortName + ' ' + utcDate.DateLongName
    ,f.*
from
    FactTableName  AS f

    -- Local Date and Local Time joins          
    inner join dbo.Date  AS localDate
        on localDate.DateKey = f.LocalDateKey

    inner join dbo.Time  AS localTime
        on localTime.TimeKey = f.LocalTimeKey 

    -- Utc Date and Utc Time joins    
    inner join dbo.Date  AS utcDate
        on utcDate.DateKey = f.UtcDateKey

    inner join dbo.Time  AS utcTime
        on utcTime.TimeKey = f.UtcTimeKey

In closing...

As you're building a data mart, and not an OLTP database, the generation of the Local and Utc times should be performed in your ETL, NOT in any client side applications for the following reasons (apart from localisation of the UTC time to the report reader's perspective):

Having the calculation reside in any queries places an extra performance burden on them, multiplied by the number of times you have to run said query for any reports you have (this matters when reading millions of rows)
Extra burden of ensuring the calculation is maintained correctly in each query (especially when you take daylight savings time into account)
Prevent range scanning of any indexes the column is part of, as you'll be performing a calculation on the column which forces queries to perform index scans instead of seeks (which are typically more expensive as each data page is needed to be read); this is known as being non-sargable.
- Edit due to comments: This applies if you push the conversion down into the actual query.
Using the concept of having the additional UTC dates and times available, there is nothing stopping you from taking this concept and extending it by calling this StandardisedDateKey, or CorporateHQDateKey, where instead of a UTC date table you standardise based on some other business agreed standard
Having the two separate column types (Local and UTC), allows for side-by-side comparison across geographic distance. Think -> someone in Australia enters a record that is timestamped with both Local and UTC, someone in New York reads the report with the Local (Australia) date and time and the New York representation of the UTC date and time, thereby seeing that something their Australian counterpart did during the middle of the day (Australia time) happened in the middle of the night their time (New York time). This comparison of time is indispensable in multi-national businesses.

MySQL – Finding the First Date in a Week Without Exceeding the Month

I hope to understand your question. You want translate a date to a customizable first day of week. I call it start_day_of_week. So if your start_day_of_week is greater than day_of_weeek you must subtract the difference otherwise you must first subtract seven day (i.e. translate to previous week) and then add the difference. For example:

if your date is Fri (day of week 6) and you want to start at Thu (day of week 5) you subtract 1 day
if your date is Fri (day of week 6) and you want to start at Sat (day of week 7) you must first subtract 7 day i.e. go to the previous week and then add 1 day.

I think it better to write a mysql function that takes as parameters a day, a customizable start_day_of_week and returns a date translated.

To begin, I write some test. I use a table with three columns. One for input date, and input start_day_of_week, and a column with the result date. So the date '2014-10-23' translates to date '2014-10-19' when the start_day_of_week is Sunday (1), but it translates to '2014-10-17' when the start day is Friday (7).

use test;
-- (1 = Sunday, 2 = Monday, …, 7 = Saturday).
drop table if exists fixture;
create temporary table fixture (input date, result date, day_of_week tinyint);
insert into fixture values
('2014-10-01', '2014-10-01', 1),
('2014-10-03', '2014-10-01', 1),
('2014-10-09', '2014-10-05', 1),
('2014-10-16', '2014-10-12', 1),
('2014-10-23', '2014-10-19', 1),

('2014-10-01', '2014-10-01', 2),
('2014-10-03', '2014-10-01', 2),
('2014-10-09', '2014-10-06', 2),
('2014-10-16', '2014-10-13', 2),
('2014-10-23', '2014-10-20', 2),

('2014-10-01', '2014-10-01', 6),
('2014-10-03', '2014-10-03', 6),
('2014-10-09', '2014-10-03', 6),
('2014-10-16', '2014-10-10', 6),
('2014-10-23', '2014-10-17', 6);

The function is:

drop function if exists mydatef;
delimiter $$
create function mydatef (d date, my_start_day_of_week int) returns date deterministic
begin
  set @v := IF(my_start_day_of_week - DAYOFWEEK(d) <= 0,
               DATE_ADD(d, INTERVAL(my_start_day_of_week - DAYOFWEEK(d)) DAY),
               DATE_ADD(d, INTERVAL(my_start_day_of_week - DAYOFWEEK(d)  - 7) DAY));
  return
    IF(@v < DATE_FORMAT(d ,'%Y-%m-01'), DATE_FORMAT(d ,'%Y-%m-01'), @v);
END;
$$
delimiter ;

So to test the function I write a simple query:

mysql> select input, day_of_week, mydatef(input, day_of_week),           
 if(result = mydatef(input, day_of_week), 'OK', 'ERR' ) as result from fixture;
+------------+-------------+-----------------------------+--------+
| input      | day_of_week | mydatef(input, day_of_week) | result |
+------------+-------------+-----------------------------+--------+
| 2014-10-01 |           1 | 2014-10-01                  | OK     |
| 2014-10-03 |           1 | 2014-10-01                  | OK     |
| 2014-10-09 |           1 | 2014-10-05                  | OK     |
| 2014-10-16 |           1 | 2014-10-12                  | OK     |
| 2014-10-23 |           1 | 2014-10-19                  | OK     |
| 2014-10-01 |           2 | 2014-10-01                  | OK     |
| 2014-10-03 |           2 | 2014-10-01                  | OK     |
| 2014-10-09 |           2 | 2014-10-06                  | OK     |
| 2014-10-16 |           2 | 2014-10-13                  | OK     |
| 2014-10-23 |           2 | 2014-10-20                  | OK     |
| 2014-10-01 |           6 | 2014-10-01                  | OK     |
| 2014-10-03 |           6 | 2014-10-03                  | OK     |
| 2014-10-09 |           6 | 2014-10-03                  | OK     |
| 2014-10-16 |           6 | 2014-10-10                  | OK     |
| 2014-10-23 |           6 | 2014-10-17                  | OK     |
+------------+-------------+-----------------------------+--------+
15 rows in set (0.00 sec)

So to respond you answer

SET @date:='2014-10-03';
-- 5 is Thursday
mysql> select mydatef(@date, 5);
+-------------------+
| mydatef(@date, 5) |
+-------------------+
| 2014-10-02        |
+-------------------+
1 row in set (0.00 sec)

-- 2 is Tuesday
mysql> select mydatef(@date, 2);
+-------------------+
| mydatef(@date, 2) |
+-------------------+
| 2014-10-01        |
+-------------------+
1 row in set (0.00 sec)

Best Answer

Related Solutions

Sql-server – Handling time zones in data mart/warehouse

Firstly...

You say...

In closing...

MySQL – Finding the First Date in a Week Without Exceeding the Month

Related Question