June 22, 2017 at 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())))
June 22, 2017 at 1:57 pm
jcelko212 32090 - Thursday, June 22, 2017 12:43 PMFor 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
June 22, 2017 at 2:11 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
June 22, 2017 at 3:09 pm
komal145 - Thursday, June 22, 2017 1:55 PMThanks 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
June 22, 2017 at 3:42 pm
Jeff Moden - Thursday, June 22, 2017 1:57 PMjcelko212 32090 - Thursday, June 22, 2017 12:43 PMFor 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.
June 22, 2017 at 3:45 pm
Jeff Moden - Thursday, June 22, 2017 3:09 PMkomal145 - Thursday, June 22, 2017 1:55 PMThanks 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.
June 22, 2017 at 4:59 pm
Ed Wagner - Thursday, June 22, 2017 3:45 PMJeff Moden - Thursday, June 22, 2017 3:09 PMkomal145 - Thursday, June 22, 2017 1:55 PMThanks 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!
June 22, 2017 at 8:44 pm
Ed Wagner - Thursday, June 22, 2017 3:42 PMJeff Moden - Thursday, June 22, 2017 1:57 PMjcelko212 32090 - Thursday, June 22, 2017 12:43 PMFor 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
June 23, 2017 at 5:19 am
Jeff Moden - Thursday, June 22, 2017 1:57 PMjcelko212 32090 - Thursday, June 22, 2017 12:43 PMFor 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".
June 23, 2017 at 5:47 am
gvoshol 73146 - Friday, June 23, 2017 5:19 AMJeff Moden - Thursday, June 22, 2017 1:57 PMjcelko212 32090 - Thursday, June 22, 2017 12:43 PMFor 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
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply