What does date show?

Question

What does date show?

JeremyU

SSChasing Mays

Points: 640
More actions
January 19, 2023 at 10:32 pm

#4139765

Hello
I would like to ask you what does it show? From today and - 1 week or just last week?
cast(DATEADD(dd, DATEPART(DW,GETDATE())*-1, GETDATE()) as date)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Gosh, Jeremy. Put a SELECT on it, run it, and see. If you can't guess that, then break out pieces and see what it comes up with for values. If you don't know the functions, look them up and science it out. For example, what does this do?

SELECT DATEPART(DW,GETDATE());

If you multiply that by -1, what does that do? If you add that resulting negative number of days to today, what does that do? If it's still not making any sense, the try changing GETDATE() to different dates and it'll become real obvious as to what the code is doing even if you can't science out the date math that's doing it.

I'd also avoid the number (DATEPART) version of DW like the plague because it changes if DATEFIRST is modified.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JeremyU SSChasing Mays Points: 640 More actions · Answer 2

Jeff you are right. It shows last week. My apology for this question. Thank you for helping.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Don't apologize for the question. You got a good part of it right. Just not the "fine point". Very specifically, which part of last week does it ALWAYS show?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Remember what I said about the DW datepart and DATEFIRST? What happens to the output when you change DATEFIRST and how does it relate to DATEFIRST insofar as the first day of a week that DATEFIRST assigns?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

To continue the math behind all of this and what the verbal description of what the code actually does, run the following code and let me know what number it returns.

SELECT @@DATEFIRST;

I promise I'll explain it but wanted you to give it a try first. I need to know what the code above returns so I can 'splain it for ya. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)