determing days of the week - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

determing days of the week

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2010-09-30 : 17:34:11

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)

7020 Posts

Posted - 2010-09-30 : 17:55:10

[code]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[/code]
Results:
[code]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[/code]

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

dougancil
Posting Yak Master

217 Posts

Posted - 2010-10-01 : 10:34:28

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?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-01 : 11:46:58

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-01 : 12:04:53

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)

dougancil
Posting Yak Master

217 Posts

Posted - 2010-10-01 : 12:11:37

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-01 : 13:58:42

The code I posted does what you want. Just change the input date to whatever you want to use.

CODO ERGO SUM

dougancil
Posting Yak Master

217 Posts

Posted - 2010-10-01 : 14:03:02

Michael,

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-01 : 14:55:58

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources