August 21, 2008 at 10:04 pm
Hi,
I have a table with 3 fields(fromdate,todate and day). Iwant to retrieve number of days between that date in mssql.
August 21, 2008 at 10:29 pm
I believe this post is in the wrong forum but you're probably after the DATEDIFF function.
e.g.
DATEDIFF(d, @thisdate, @nextmonth)
August 22, 2008 at 1:34 am
I WANT A PARTICULAR DATE MEANS SUNDAY,MONDAY ETC....
August 22, 2008 at 1:24 pm
If you are trying to do this in T-Sql then create a function that will return the desired string. Here is the code.
CREATE Function DaysBetweenDates
(
@FromDate smalldatetime,
@ToDate smalldatetime
)
RETURNS varchar(4000)
AS
BEGIN
DECLARE
@DaysInBetween varchar(4000)
SET @DaysInBetween = ''
WHILE @FromDate < @ToDate
BEGIN
SET @DaysInBetween = @DaysInBetween + CASE DATEPART(dw, @FromDate)
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUESDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY'
END + ','
SET @FromDate = @FromDate + 1
END
RETURN LEFT(@DaysInBetween, LEN(@DaysInBetween) -1) --Just to remove the extra ',' at the end
END
You can call this function as
SELECT dbo.DaysBetweenDates(getdate()-10,getdate())
August 24, 2008 at 7:59 pm
shall i get this sql server 2005
August 24, 2008 at 11:06 pm
Mukti (8/22/2008)
If you are trying to do this in T-Sql then create a function that will return the desired string. Here is the code.
CREATE Function DaysBetweenDates(
@FromDate smalldatetime,
@ToDate smalldatetime
)
RETURNS varchar(4000)
AS
BEGIN
DECLARE
@DaysInBetween varchar(4000)
SET @DaysInBetween = ''
WHILE @FromDate < @ToDate
BEGIN
SET @DaysInBetween = @DaysInBetween + CASE DATEPART(dw, @FromDate)
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUESDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY'
END + ','
SET @FromDate = @FromDate + 1
END
RETURN LEFT(@DaysInBetween, LEN(@DaysInBetween) -1) --Just to remove the extra ',' at the end
END
You can call this function as
SELECT dbo.DaysBetweenDates(getdate()-10,getdate())
You don't need a While loop and you don't need to build a special function... SQL Server already has one...
If you don't already have a Tally table, you need to get one... please see the following article for what it is and how it works. Computationally speaking, it's worth it's weight in gold.
http://www.sqlservercentral.com/articles/TSQL/62867/
Once you have that, this whole thing becomes childs play...
--===== Declare the local variables
DECLARE @FromDate DATETIME,
@ToDate DATETIME
--===== Preset the from and to dates to something
SELECT @FromDate = DATEADD(dd,DATEDIFF(dd,0,GETDATE()-125),0), --Some day 125 days ago or whatever with time stripped
@ToDate = GETDATE()+132 --Some day 132 days from now or whatever.
--===== Solve th problem
SELECT @FromDate + t.N-1 AS TheDate,
DATENAME(dw,@FromDate + t.N-1) AS TheDay
FROM dbo.Tally t
WHERE @FromDate + t.N-1 <= @ToDate
Seriously... make the Tally table... it's got dozens of uses.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 24, 2008 at 11:09 pm
... and, if that's not the right answer, then tell us exactly what you want by giving us an example of the inputs provided and a printout of what you'd like to see the output as. We're just guessing here.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 24, 2008 at 11:20 pm
I want the count each day seperately
August 25, 2008 at 9:21 pm
aneeshvarghes (8/24/2008)
I want the count each day seperately
Still doesn't tell me a thing... like I said, tell us exactly what you want by giving us an example of the inputs provided and a printout of what you'd like to see the output as. We're just guessing here.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 25, 2008 at 11:28 pm
If from date is 1/1/2007 and todate is 8/8/2007. then i want the count of number of sundays,mondays,tuesdays,........,saturdays seperately
August 25, 2008 at 11:38 pm
So take one of the solutions already given and use it as a derived table in and aggregate query.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 27, 2008 at 2:32 pm
Here's a query hat outputs what I'm thinking you want. You could easily turn it into a function that returns a table.
DECLARE
@FromDate smalldatetime,
@ToDate smalldatetime
DECLARE
@DaysFromTo TABLE (DayInBetween datetime)
select @FromDate = '1/1/2007', @ToDate = '8/8/2007'
WHILE @FromDate < @ToDate
BEGIN
INSERT @DaysFromTo
SELECT @FromDate
SET @FromDate = @FromDate + 1
END
SELECT
CASE DATEPART(dw, DayInBetween)
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUESDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY'
END AS DayInWeek,
count(*) Frequency
FROM
@DaysFromTo
GROUP BY
CASE DATEPART(dw, DayInBetween)
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUESDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY'
END
August 28, 2008 at 6:52 am
This gives you a count of each day of the week between any two dates (inclusive).
DECLARE @FromDate smalldatetime
DECLARE @ToDate smalldatetime
DECLARE @TotalDays int
DECLARE @LeftoverDays int
DECLARE @FullWeeks int
DECLARE @Out TABLE(Sun int, Mon int, Tue int, Wed int, Thu int, Fri int, Sat int)
DECLARE @FromDay int
DECLARE @ToDay int
SET @FromDate = '1/1/2008'
SET @ToDate = '1/25/2008'
SET @TotalDays = DateDiff(d, @FromDate, @ToDate) + 1 --Add one to include the ToDate
SET @LeftoverDays = @TotalDays % 7
SET @FullWeeks = (@TotalDays - @LeftoverDays) / 7
SET @FromDay = DATEPART(dw, @FromDate)
SET @ToDay = DATEPART(dw, @ToDate)
/*
SELECT @FromDate '@FromDate', @ToDate '@ToDate',
@TotalDays '@TotalDays', @LeftoverDays '@LeftoverDays',
@FullWeeks '@FullWeeks',
@FromDay '@FromDay',
@ToDay '@ToDay'
*/
INSERT INTO @Out (Sun, Mon, Tue, Wed, Thu, Fri, Sat)
VALUES (@FullWeeks, @FullWeeks, @FullWeeks, @FullWeeks, @FullWeeks, @FullWeeks, @FullWeeks)
--SELECT (@FromDay - 1) '@FromDay', (@FromDay - 1) + @LeftoverDays - 1 'EndDay', ((@FromDay - 1) + @LeftoverDays - 1) %7 'EndDay2', @DaysWrapped '@DaysWrapped'
DECLARE @DaysWrapped int
SET @DaysWrapped = CASE WHEN (((@FromDay - 1) + @LeftoverDays - 1) %7) < (@FromDay - 1) THEN 1 ELSE 0 END
UPDATE @Out
SET Sun = Sun + CASE @DaysWrapped WHEN 0
THEN CASE WHEN @FromDay <= 1 AND 1 <= @ToDay THEN 1 ELSE 0 END
ELSE CASE WHEN @FromDay <= 1 OR 1 <= @ToDay THEN 1 ELSE 0 END END,
Mon = Mon + CASE @DaysWrapped WHEN 0
THEN CASE WHEN @FromDay <= 2 AND 2 <= @ToDay THEN 1 ELSE 0 END
ELSE CASE WHEN @FromDay <= 2 OR 2 <= @ToDay THEN 1 ELSE 0 END END,
Tue = Tue + CASE @DaysWrapped WHEN 0
THEN CASE WHEN @FromDay <= 3 AND 3 <= @ToDay THEN 1 ELSE 0 END
ELSE CASE WHEN @FromDay <= 3 OR 3 <= @ToDay THEN 1 ELSE 0 END END,
Wed = Wed + CASE @DaysWrapped WHEN 0
THEN CASE WHEN @FromDay <= 4 AND 4 <= @ToDay THEN 1 ELSE 0 END
ELSE CASE WHEN @FromDay <= 4 OR 4 <= @ToDay THEN 1 ELSE 0 END END,
Thu = Thu + CASE @DaysWrapped WHEN 0
THEN CASE WHEN @FromDay <= 5 AND 5 <= @ToDay THEN 1 ELSE 0 END
ELSE CASE WHEN @FromDay <= 5 OR 5 <= @ToDay THEN 1 ELSE 0 END END,
Fri = Fri + CASE @DaysWrapped WHEN 0
THEN CASE WHEN @FromDay <= 6 AND 6 <= @ToDay THEN 1 ELSE 0 END
ELSE CASE WHEN @FromDay <= 6 OR 6 <= @ToDay THEN 1 ELSE 0 END END,
Sat = Sat + CASE @DaysWrapped WHEN 0
THEN CASE WHEN @FromDay <= 7 AND 7 <= @ToDay THEN 1 ELSE 0 END
ELSE CASE WHEN @FromDay <= 7 OR 7 <= @ToDay THEN 1 ELSE 0 END END
WHERE @LeftoverDays > 0
SELECT * FROM @Out
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply