How to get the monday of every week

Question

How to get the monday of every week

Viewing 10 posts - 16 through 24 (of 24 total)

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

komal145 SSCrazy Eights Points: 9915 More actions · Answer 1

Thanks everyone was able to get the monday's from Getdate()

SELECT DATEADD(wk, -5, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
SELECT DATEADD(wk, -4, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
SELECT DATEADD(wk, -3, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
SELECT DATEADD(wk, -2, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
SELECT DATEADD(wk, -1, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
SELECT DATEADD(wk, 0, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

jcelko212 32090 - Thursday, June 22, 2017 12:43 PM
For example, you might be coded "MP" and not be a Member of Parliament, but actually a "male pseudo-hermaphrodite" instead.). .

BWAAA-HAAA! Maybe you might, but not me. 😉

Crazy world. They even have standards about how to have meetings to decide how to write standards about how to write standards. Considering how poorly many of the standards have been written, especially in the areas of implementation and enforcement, I'm very happy that I don't actually have to deal with any of them anymore.

--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)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

Most of the world uses ISO 5218 (0 = unknown, 1 = male, 2 = female, 9 = lawful person)

I've never seen that, and I've been in IT for decades including at $25B+ companies. Nearly all use 'M' / 'F' (or whatever matches in the local language).

Data must also be relevant for the company to want it. Perhaps a medical company would care about all the variations, but most do not.

On SSC itself, for example, the choices are simply "Male" or "Female": hermaphrodite status not included :-).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

komal145 - Thursday, June 22, 2017 1:55 PM
Thanks everyone was able to get the monday's from Getdate()
SELECT DATEADD(wk, -5, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
SELECT DATEADD(wk, -4, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
SELECT DATEADD(wk, -3, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
SELECT DATEADD(wk, -2, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
SELECT DATEADD(wk, -1, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
SELECT DATEADD(wk, 0, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))

So you have 15-16 lines of nearly identical hard-coded code in your code to solve your original problem. While it will certainly work fine to solve your original problem, I strongly recommend you go back an look at some of the solutions offered and figure out what they actually do so that when you run into a similar problem with thousands of returned rows required, you'll be able to handle it without thousands of hard coded rows.

--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)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Jeff Moden - Thursday, June 22, 2017 1:57 PM
jcelko212 32090 - Thursday, June 22, 2017 12:43 PM
For example, you might be coded "MP" and not be a Member of Parliament, but actually a "male pseudo-hermaphrodite" instead.). .
BWAAA-HAAA! Maybe you might, but not me. 😉
Crazy world. They even have standards about how to have meetings to decide how to write standards about how to write standards. Considering how poorly many of the standards have been written, especially in the areas of implementation and enforcement, I'm very happy that I don't actually have to deal with any of them anymore.

AMEN to that! Some people are so obsessed with standards that they forget to get the work done.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

Jeff Moden - Thursday, June 22, 2017 3:09 PM
komal145 - Thursday, June 22, 2017 1:55 PM
Thanks everyone was able to get the monday's from Getdate()
SELECT DATEADD(wk, -5, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
SELECT DATEADD(wk, -4, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
SELECT DATEADD(wk, -3, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
SELECT DATEADD(wk, -2, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
SELECT DATEADD(wk, -1, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
SELECT DATEADD(wk, 0, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
So you have 15-16 lines of nearly identical hard-coded code in your code to solve your original problem. While it will certainly work fine to solve your original problem, I strongly recommend you go back an look at some of the solutions offered and figure out what they actually do so that when you run into a similar problem with thousands of returned rows required, you'll be able to handle it without thousands of hard coded rows.

Jeff's point here is an important one. What happens when you want to make a table of Mondays for a year's worth of attendance? What if you want to amortize money over a 30-year loan? What if you want to track a star across the sky for 1000 years? What if you want to try something even more astronomical like calculating how much it would take to pay off the national debt at a base of 5000 years? 😛

Do yourself a favor up front and think about the set-based approach. Using a function will save you keystrokes now and a lot of headache later.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 7

Ed Wagner - Thursday, June 22, 2017 3:45 PM
Jeff Moden - Thursday, June 22, 2017 3:09 PM
komal145 - Thursday, June 22, 2017 1:55 PM
Thanks everyone was able to get the monday's from Getdate()
SELECT DATEADD(wk, -5, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
SELECT DATEADD(wk, -4, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
SELECT DATEADD(wk, -3, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
SELECT DATEADD(wk, -2, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
SELECT DATEADD(wk, -1, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
SELECT DATEADD(wk, 0, DATEADD(DAY, -5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE())))
So you have 15-16 lines of nearly identical hard-coded code in your code to solve your original problem. While it will certainly work fine to solve your original problem, I strongly recommend you go back an look at some of the solutions offered and figure out what they actually do so that when you run into a similar problem with thousands of returned rows required, you'll be able to handle it without thousands of hard coded rows.
Jeff's point here is an important one. What happens when you want to make a table of Mondays for a year's worth of attendance? What if you want to amortize money over a 30-year loan? What if you want to track a star across the sky for 1000 years? What if you want to try something even more astronomical like calculating how much it would take to pay off the national debt at a base of 5000 years? 😛
Do yourself a favor up front and think about the set-based approach. Using a function will save you keystrokes now and a lot of headache later.

To pile on, keep in mind that this approach is sensitive to your DATEFIRST, unlike some of the fine solutions others have offered here.

Cheers!

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

Ed Wagner - Thursday, June 22, 2017 3:42 PM
Jeff Moden - Thursday, June 22, 2017 1:57 PM
jcelko212 32090 - Thursday, June 22, 2017 12:43 PM
For example, you might be coded "MP" and not be a Member of Parliament, but actually a "male pseudo-hermaphrodite" instead.). .
BWAAA-HAAA! Maybe you might, but not me. 😉
Crazy world. They even have standards about how to have meetings to decide how to write standards about how to write standards. Considering how poorly many of the standards have been written, especially in the areas of implementation and enforcement, I'm very happy that I don't actually have to deal with any of them anymore.
AMEN to that! Some people are so obsessed with standards that they forget to get the work done.

I'm all for good standards. The ANSI/ISO standards are great standards. Since the manufacturers of RDBMS systems don't follow those standards and the standards are frequently missing important features and methods as are the various RDBMSs themselves (not to mention even a standardization on the declaration of variables, for example) making true portability and certain supposed "best practices" a myth, the standards should be used as a guide and not gospel.

--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)

GaryV Hall of Fame Points: 3307 More actions · Answer 9

Jeff Moden - Thursday, June 22, 2017 1:57 PM
jcelko212 32090 - Thursday, June 22, 2017 12:43 PM
For example, you might be coded "MP" and not be a Member of Parliament, but actually a "male pseudo-hermaphrodite" instead.). .
BWAAA-HAAA! Maybe you might, but not me. 😉
Crazy world. They even have standards about how to have meetings to decide how to write standards about how to write standards. Considering how poorly many of the standards have been written, especially in the areas of implementation and enforcement, I'm very happy that I don't actually have to deal with any of them anymore.

I recently came across an application that uses 6 values for gender, including "Ambiguous".

Thom A SSC Guru Points: 99130 More actions · Answer 10

gvoshol 73146 - Friday, June 23, 2017 5:19 AM
Jeff Moden - Thursday, June 22, 2017 1:57 PM
jcelko212 32090 - Thursday, June 22, 2017 12:43 PM
For example, you might be coded "MP" and not be a Member of Parliament, but actually a "male pseudo-hermaphrodite" instead.). .
BWAAA-HAAA! Maybe you might, but not me. 😉
Crazy world. They even have standards about how to have meetings to decide how to write standards about how to write standards. Considering how poorly many of the standards have been written, especially in the areas of implementation and enforcement, I'm very happy that I don't actually have to deal with any of them anymore.
I recently came across an application that uses 6 values for gender, including "Ambiguous".

Try horses. They have 7 Genders... /facepalm

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk