Get Date & DateName Help - newbie questions.
-
I need help with GetDate and/or DateName functions to put my logic into code. I am a Manufacturing Engineer not an IT professional, so I know enough programming to be really dangerous.
We have a meeting every Wednesday to discuss the status of certain part numbers. When a new part number is created in the database, certain action items need to be automatically assigned (through code) a due date on 3 Wednesday’s from whatever today is.
Here is the code logic I would use, please correct me…
Use the DateName(dw, GetDate()) to get today’s weekday name (Monday, Tuesday, etc.) and use the Case function to assign an integer to the name (Thursday = 5). I would then need to compare today’s integer (5) to Wednesday’s meeting day (4) and add the difference to the 21 days (3 weeks). If we use today for example (Thursday) we would add 6 days to the 21 days.
Is the logic correct? Can someone please help me decipher this logic (if correct) to actual code?
Thanks,
Mark
-
Try the following (it assumes the following default: set datefirst 7 (Sunday is the first day of the week)):
select dateadd(d, (7 - ((datepart(dw, getdate()) + 3) % 7) + 21), getdate())
-
Well that was about 1000 steps easier than I thought. Thanks!
Now can someone explain how this works? I see there are 2 "functions", dateadd adn datepart but how do either of them work?
Thanks,
Mark
-
I could try to explain how they work, or better yet, I could just have you read BOL first and then if you have any questions, we can can talk. I think BOL does a good job of providing basic information.
-
How the whole thing works:
Based on Sunday as the first day of the week:
Day Num Days to Add
Sun 1 3
Mon 2 2
Tue 3 1
Wed 4 7
Thu 5 6
Fri 6 5
Sat 7 4
The problem is getting from Num to Days to Add. If you look at it, though you start to
see a pattern. Start with Sunday, you want to add 3 to 21 to determine when you should meet. If you add 3 to 1 and subtract that sum from 7, you get 3. Follow that same pattern for Monday. Add 3 to 2 and subtract from 7, you get 2. Tuesday works the same way as Sunday and Monday. Now you come to Wednesday, and using the formula above you 7 - (4 + 3) = 0. That isn't right,
it needs to be 7. Thursday looks like this: 7 - (5 + 3) = -1, again wrong as it needs to be 6.
Add the modulo funtion (%). Wednesday: 7 - ((4 + 3) % 7) = 7 (7 mod 7 = 0), Thursday: 7 - ((5 + 3) % 7) = 6.
Go back to Monday: 7 - ((2 + 3) % 7) = 2 (5 mod 7 = 5).
Does that help explain how this works?
-
After you posted your solution (pretty slick, btw) I tried to figure out on my own how you did it and arrived at a slightly messier version:
Sunday= 1, DaysToWednesday= 3, RotateBackThree=7, Invert= 1 Monday= 2, DaysToWednesday= 2, RotateBackThree=6, Invert= 2 Tuesday= 3, DaysToWednesday= 1, RotateBackThree=5, Invert= 3 Wednesday= 4, DaysToWednesday= 7, RotateBackThree=4, Invert= 4 Thursday= 5, DaysToWednesday= 6, RotateBackThree=3, Invert= 5 Friday= 6, DaysToWednesday= 5, RotateBackThree=2, Invert= 6 Saturday= 7, DaysToWednesday= 4, RotateBackThree=1, Invert= 7 Invert RotateForwardThree (7 - 1) + 1 = 7 ((7 + 3) - 1) % 7) + 1 = 3 (7 - 2) + 1 = 6 ((6 + 3) - 1) % 7) + 1 = 2 (7 - 3) + 1 = 5 ((5 + 3) - 1) % 7) + 1 = 1 (7 - 4) + 1 = 4 ((4 + 3) - 1) % 7) + 1 = 7 (7 - 5) + 1 = 3 ((3 + 3) - 1) % 7) + 1 = 6 (7 - 6) + 1 = 2 ((2 + 3) - 1) % 7) + 1 = 5 (7 - 7) + 1 = 1 ((1 + 3) - 1) % 7) + 1 = 4 ((7 - X + 1 + 3 - 1) % 7) + 1 ((7 - X + 3) % 7) + 1 select dateadd(d, ((7 - datepart(dw, getdate()) + 3) % 7) + 1 + 14, getdate())
-
I majored in Computer Science, I minored in Mathematics. Looking for patterns and solutions to them seems to come easy, especially when math is involved.
Hope this helps with what you are working on!
-
Before that I was thinking along these lines:
declare @startdate datetime, @enddate datetime declare @dw_startdate int select @startdate = getdate() select @dw_startdate = ((datepart(dw, @startdate) + @@DATEFIRST -1)%7)+1 select @enddate = @startdate + (4-@dw_startdate) + case when @dw_startdate < 4 then 14 else 21 endYour way is more compact.
-
...except, it doesn't work... if today is the 10th of November, 2006, the 3rd Wednesday from today is on the 29th of November... not the 6th of December...
SET DATEFIRST 7
select dateadd(d, (7 - ((datepart(dw, '20061110') + 3) % 7) + 21), '20061110')
------------------------------------------------------
2006-12-06 00:00:00.000
(1 row(s) affected)
--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) -
Based on what he was looking for, 2006-12-06 is the correct answer. Found the next Wednesday meeting and added 21. If you wanted the third Wednesday, change the 21 to 14.
-
Thought this was what he asked for...
"certain action items need to be automatically assigned (through code) a due date on 3 Wednesday’s from whatever today is. "
I agree, the 14 does the trick.
--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) -
From his first post:
If we use today for example (Thursday) we would add 6 days to the 21 days.
That looks like a totla of adding 27 days to todays date.
Lynn
-
Sure, that's what he said he thought the logic should be... he said immediately after that...
"Is the logic correct? Can someone please help me decipher this logic (if correct) to actual code?"
His logic was not correct...
--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) -
Well, He has the logic, and the theory behind it. Now he has to apply it and adjust it to his environment. I have asked for help, gotten advice, applied it and had to adjust it to the realities of the environment because it was quite right.
Our ultimate goal is help those stuck to see the light, not to do their work for them. He even went so fas as to come up with a solution as well. I give him 3 thumbs up for the doing that, as it shows me he wants to learn something instead of having us hand him the solution and not know how it works.
Lynn
-
Well, when folks post such detailed information about how their code works (very nice job, by the way), others take the code and just use it instead of analyzing it. He specifically asked for the 3rd Wednesday, you gave him the 4th. Even though it's not your intent to do the work for him, that's what you did and what his perception is. Notice that he didn't come back and say "Lynn, I tested your great code and found that it gave me the 4th Wednesday. It does work if I change the 21 to a 14, though."
--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)
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply