Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
geo039
Starting Member
8 Posts |
 Posted - 2010-07-15 : 14:25:48
|
| I have a query that shows or hides a button in my application based on dates of a pay period. The button should show up in the current pay period and the prior. So as of now the button should show June 20th 12:00 am till July 17th at 11:59 pm. The problem is the dates in the table end July 17th at 12 am when technically it needs to keep showing the button till July 17th 11:59 pm. I am trying to modify the query to add 24 hours or 23 hours and 59 minutes but no matter how I change the datadd piece the button disappears.I need assistance figuring out how to modify the query. The new pay period starts sunday 12:00 am so the button needs to stay on till Saturday 11:59 pm in two weeksALTER PROCEDURE [dbo].[tsShowUnlockButton] (@bdate datetime, @ShowButton bit output) ASdeclare @dtFirst datetime, @dtLast datetime, @dtCurrentBegin datetimeselect @dtCurrentBegin=Begin_Date, @dtLast=End_Date from PayPeriods where GetDate() between Begin_Date and End_Dateselect @dtFirst=Begin_Date from PayPeriods where End_Date=Dateadd(d, -1, @dtCurrentBegin)if (@bdate>=@dtFirst and @bdate<=@dtLast) set @ShowButton=1else set @ShowButton=0 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-15 : 14:34:54
|
I am not clear about:- why you need SQL to decide if a button is hidden or not- what exactly is the condition? - Is it a special system date?- Is it depending on data in a table?Looks very confusing what you are doing there.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
geo039
Starting Member
8 Posts |
Posted - 2010-07-15 : 15:57:30
|
| The pay periods are stored in a table, only users of a certain type (supervisor) can unlock the timesheet once it is approved and submitted so they can only get that button not employees. However due to personnel level processing that button should be disabled even for supervisors at a certain point. Currently the button is locking and unlocking as it should, however it's off by 23 hrs 59 minutes since the table stores the pay periods dates as the default 12 am. I just need the query i posted modified to add an addl 23 hrs 59 minutes so instead of locking Saturday at 12 am it's saturday at 11:59 pmHowever no matter how I modify this line End_Date=Dateadd(d, -1, @dtCurrentBegin) the button disappears. I mean i don't care if the thing locks at midnight sunday it's the difference of 1 minute. I tried doing End_Date=Dateadd(mi, -1, @dtCurrentBegin) and it broke, the button disappeared so maybe i don't quite understand the query (i didn't write it, i'm just stepping in). To me it says take a day off the current new begin date but that doesn't seem to be the case.I'm about to just update the pay period table and set all 12 am's to 1159 pm and be done with it LOL |
|
|
|
|
|
|
|
|
Help with DateAdd