3

FYI: I'm using SQL Server 2012.

Because scope of the function is a selling date which is different by the days of week (like comparison for customer visiting percentage).

So the main key to compare between last-this year, is to check the same week or same day that most related to the month and are same day (Monday, Tuesday, ...)

2018-November 2017-November

For example:

2018/11/01 should compare to 2017/11/02 (Thu)
2018/11/02 should compare to 2017/11/03 (Fri)
...
2018/11/30 should compare to 2017/12/01 (Fri)

I can't imagine the syntax out of my head. What is syntax should I write? What are the keywords, functions should I call to achieve this scope?

Anyone who has experience about some kind of function like this, please help. All comments would be appreciate.

3
  • Apologized, I didn't know that the specific product be related that much. I'm using SQL Server - Web 64 bit v.11.0.6020.0 => 2012 – Natta Wang Nov 20 '18 at 8:57
  • Would 2018/10/31 compare to 2017/11/01? Where is the base logic for comparing? Possibly: Week Number + Weekday? – John K. N. Nov 20 '18 at 13:44
  • @hot2use Should comparing start from 2018/11/01 to 2018/11/30 (scope on the month that is focusing) and look like the Week Number AND Weekday will approach what I aim. Thanks for your guide. – Natta Wang Nov 21 '18 at 3:42
4

If I correctly understand your question, you can use two (ore more) ways but both are related with DATEPART function.

DECLARE @Date1 datetime = '20181101';
DECLARE @Date2 datetime = '20170101';

SELECT DATEPART(DAYOFYEAR, @Date1) DayOfYear,
       DATEPART(WEEK, @Date1) WeekNumber,
       DATEPART(WEEKDAY, @Date1) WeekDay,
       @@DATEFIRST FirstDayOfWeek;

DayOfYear | WeekNumber | WeekDay | FirstDayOfWeek
--------: | ---------: | ------: | :-------------
      305 |         44 |       5 | 7

Using DayOfYear

SELECT DATEADD(DAY, DATEPART(DAYOFYEAR, @Date1), @Date2) AS UsingDayOfYear;

| UsingDayOfYear      |
| :------------------ |
| 02/11/2017 00:00:00 |

Using same week & same day of week

This depends on first day of week:

SET DATEFIRST 1;

SELECT DATEADD(DAY, ((DATEPART(WEEK, @Date1) - 1) * 7) + DATEPART(WEEKDAY, @Date1), @Date2) UsingSameMonthSameDayOfWeek;

UsingSameMonthSameDayOfWeek |
| :-------------------------- |
| 02/11/2017 00:00:00         |

You can use that function to add another combination, for example: First Thursday of November.

db<>fiddle here

2
  • Thanks, it seem I got a clue from your answer, then I can solve this. So I would prefer the Week Number AND Day of Week method, because when I write the syntax, it become more natural sentence than DayOfYear method. – Natta Wang Nov 21 '18 at 6:27
  • I'm glad to help. – McNets Nov 21 '18 at 7:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.