Date Query

Hi
I am trying to write a SQL statement that gives me a date in the
future. The requirements are:-
The date should be Wednesday at 17.30.
The date must be at least 5 full days in advance.
So if it is Monday 1st, return Wednesday 10th 17.30.
If it is Friday 5th 17.29, return Wednesday 10th 17.30.
If it is Friday 5th 17.31, return Wednesday 17th 17.30.
Any help would be appreciated!
Thanks
GTry this one here (partly tested)
ALTER Function NextWantedDay
(
@.Startdate datetime,
@.EstWeekday INT,
@.EstTime DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @.EstDate DATETIME
IF (DATEPART(dw,@.Startdate) = @.EstWeekday AND
CONVERT(VARCHAR(10),@.Startdate,108) <=CONVERT(VARCHAR(10),@.EstTime,108))
RETURN
CONVERT(VARCHAR(50),CONVERT(Varchar(10),@.Startdate,112),113) + @.EstTime
BEGIN
SET @.Startdate = @.Startdate +1
WHILE DATEPART(dw,@.Startdate) <> @.EstWeekday
BEGIN
SET @.Startdate = @.Startdate + 1
END
END
RETURN CONVERT(VARCHAR(50),CONVERT(Varchar(10),@.Startdate,112),113) +
@.EstTime
END
Select dbo.NextWantedDay(getdate(),1,'09:00')
HTH, Jens Suessmeyer.

date function/tool to determine future business days/ holidays

Hi,

Does SQL server 2005 provide a function to determine future BUSINESS day ? What about holidays ? I sthere a way to feed a holiday file into a table and have SQL Server determin if the future date falls on a business day ?

As far as you do not mean the fixed calculated public holidays (like Easter, which can be calculated with a Gauss formula) you will have to implement that on your own having a custom table with the special dates.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks but I am still unclear. If I'd like to know if today's date + 100 days from now would fall on a business day . Is there a ready-to-use MS SQL function that I can use or should I write it on my own ?

|||

Well, define business day. There is not a common understanding of business day (even in one countrym like here in Germany :-) )

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hello. I think the following library of functions can help with the issue of finding Business Days:

http://www.SQLsharp.com/

I just added some date functions and one of them is finding the number of business days within a given date range. And because not everyone can agree on what is a business day, the function allows the user to select which days are to be filtered out as non-business days. I have not done a function to find if a particular day in the future is a business day, but that can be added quite easily. This function can determine holidays on any given year so you don't need the typical holiday table that most people suggest using.

date function/tool to determine future business days/ holidays

Hi,

Does SQL server 2005 provide a function to determine future BUSINESS day ? What about holidays ? I sthere a way to feed a holiday file into a table and have SQL Server determin if the future date falls on a business day ?

As far as you do not mean the fixed calculated public holidays (like Easter, which can be calculated with a Gauss formula) you will have to implement that on your own having a custom table with the special dates.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks but I am still unclear. If I'd like to know if today's date + 100 days from now would fall on a business day . Is there a ready-to-use MS SQL function that I can use or should I write it on my own ?

|||

Well, define business day. There is not a common understanding of business day (even in one countrym like here in Germany :-) )

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hello. I think the following library of functions can help with the issue of finding Business Days:

http://www.SQLsharp.com/

I just added some date functions and one of them is finding the number of business days within a given date range. And because not everyone can agree on what is a business day, the function allows the user to select which days are to be filtered out as non-business days. I have not done a function to find if a particular day in the future is a business day, but that can be added quite easily. This function can determine holidays on any given year so you don't need the typical holiday table that most people suggest using.

Date calculation

I need to create a user defined function to calculation the difference between today and a future date. The result needs to be in days, hours, and minutes formatted as per the following example: 1d / 4h / 30m. I have a moderate level of SQL exprience. however, I would appreciate some expert advice as the best way to approach this.So do you have a specific question? Are you having an issue with some part of it?|||I am not having a specific issue. I wrote the following and it works:

CREATE FUNCTION [simexdb].GetRoundTimeLeft
(
@.datetoday datetime,
@.RoundExpDate datetime
)
RETURNS varchar(50) AS
BEGIN
DECLARE @.Days int
DECLARE @.Hours int
DECLARE @.Min int
DECLARE @.TimeString as varchar(50)
SET @.Min = DATEDIFF ( mi , @.datetoday, @.RoundExpDate)
SET @.Days= @.Min/(24*60)
SET @.Min = @.Min - (@.Days*(24*60))
SET @.Hours= @.Min/60
SET @.Min = @.Min - (@.Hours*(60))
SET @.TimeString = CONVERT(varchar, @.Days ) + 'd / ' + CONVERT(varchar, @.Hours ) + 'h / ' + CONVERT(varchar, @.Min )+ 'm'
Return @.TimeString
END

I would like some expert feedback as to whether this is the best and most efficient approach.

Thank you for your response.|||That looks good|||Thank you for your confirmation.