SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 determing days of the week
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dougancil
Posting Yak Master

USA
217 Posts

Posted - 09/30/2010 :  17:34:11  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
This may be a simple question, but if I have a field in my database for the date, but I need to always figure the next Saturday from that, is that possible? So for example if my date is

9/30/2010, I'd need a result back of 10/2/2010 but if the date of 10/12/2010 is entered, the query would have to return the result of 10/16/2010.

Thank you,

Doug

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/30/2010 :  17:55:10  Show Profile  Reply with Quote
select
	a.MyDate,
	Saturday = dateadd(dd,((datediff(dd,'17530107',a.MyDate)/7)*7)+6,'17530107')

from
	( -- Test Data
	select MyDate = convert(datetime,'20100930')	union all
	select MyDate = convert(datetime,'20101012')
	) a

Results:
MyDate                   Saturday
------------------------ ------------------------ 
2010-09-30 00:00:00.000  2010-10-02 00:00:00.000
2010-10-12 00:00:00.000  2010-10-16 00:00:00.000



End of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760

Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762





CODO ERGO SUM

Edited by - Michael Valentine Jones on 10/01/2010 13:56:37
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 10/01/2010 :  10:34:28  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
Micheal,

since I'll never know what the date is going to be when it's input by the user, would your script work for that?
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/01/2010 :  11:46:58  Show Profile  Reply with Quote
quote:
Originally posted by dougancil

Micheal,

since I'll never know what the date is going to be when it's input by the user, would your script work for that?



Please explain what you are asking.




CODO ERGO SUM
Go to Top of Page

robvolk
Most Valuable Yak

USA
15678 Posts

Posted - 10/01/2010 :  12:04:53  Show Profile  Visit robvolk's Homepage  Reply with Quote
Here's a UDF version of Michael's code:
CREATE FUNCTION dbo.NextSaturday(@date DATETIME) RETURNS DATETIME AS
BEGIN 
	RETURN DATEADD(dd,((DATEDIFF(dd,'17530107',@date)/7)*7)+6,'17530107')
END
GO

SELECT dbo.NextSaturday(GETDATE())
SELECT dbo.NextSaturday(GETDATE()+2)
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 10/01/2010 :  12:11:37  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
Micheal,

This is going to be used as part of a payroll program. What I have is a popup box that shows the user when the next payroll range is, but it's based on what day that the payroll is finished. I realized that this couldnt work since I'll never know what day that someone enters and runs payroll, so in my database, I have a field marked as payrolldate. What I need is a way that no matter what day is entered into payrolldate, that I can query that field, and know what the date that the next Saturday falls on.

So for example, if a user completes entering the payroll on Tuesday the 5th this week, my query will know that the next available date that payroll can be run (starting on that Saturday) will be the 9th. Likewise if someone enters payroll on a Friday the 10th, my query will know that the next available date will be the 11th. Does that make sense?

Thank you,

Doug
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/01/2010 :  13:58:42  Show Profile  Reply with Quote
The code I posted does what you want. Just change the input date to whatever you want to use.



CODO ERGO SUM
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 10/01/2010 :  14:03:02  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
Michael,

So if I understand you correctly, by replacing the variable MyDate with whatever information I'm collecting, this should work, correct?
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/01/2010 :  14:55:58  Show Profile  Reply with Quote
quote:
Originally posted by dougancil

Michael,

So if I understand you correctly, by replacing the variable MyDate with whatever information I'm collecting, this should work, correct?



Wouldn't it be easier for you to just try it yourself to see what happens?



CODO ERGO SUM
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000