How Can I Get No Of Days From N Number Of Datetime Slots

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

Please help me
my master table is like

Id Name Fromdate Todate
1 SEASON1 2014-01-01 2014-04-30
2 SEASON2 2014-05-01 2014-08-31
3 SEASON3 2014-09-01 2014-12-31

and I got two dates as 2014-15-01 and 2014-12-20 when am passing these two date I need three
set of records aginst each season as

Name Days
Season1 106
Season2 123
Season3 111

thanks in advance

Posted 31-Jul-14 2:36am

victowork

Updated 1-Aug-14 2:04am

v2

Add a Solution

Comments

CHill60 31-Jul-14 8:37am

Not clear ... where do the 40 and 228 come from?

victowork 1-Aug-14 0:08am

40 is the no of days between 21-jan-2014 to 30-april-2014 and 228 is the no of days between
1-may-2014 to 15-dec-2014

Maciej Los 1-Aug-14 1:59am

Not true!
between 2014-01-21 and 2014-04-30 is 99 days difference (11 days of January, entire: February, March and April)
between 2014-05-01 and 2014-12-15 is 228 days difference

Maciej Los 1-Aug-14 2:10am

Provide sample data and the structure of table. Use "Improve question" widget.

3 solutions

Solution 1

You'll want to use the DateDiff function in SQL.

See:
http://msdn.microsoft.com/en-us/library/ms189794.aspx[^]

and

http://www.w3schools.com/sql/func_datediff.asp[^]

Posted 31-Jul-14 2:59am

ZurdoDev

Comments

victowork 1-Aug-14 0:10am

how can i get the days 40 is the no of days between 21-jan-2014 to 30-april-2014 and 228 is the no of days between
1-may-2014 to 15-dec-2014 using datediff

ZurdoDev 1-Aug-14 7:17am

1. 40 is not the number of days between 2014-01-21 and 2014-04-30.
2. Did you look at how to use datediff? If you want the number of days between 2 dates, use DateDiff

Solution 2

Try this logic ,it works

SQL

declare @fdate datetime = '01/15/2014'
declare @tdate datetime = '12/20/2014'

select Name, DATEDIFF(DAY, CASE WHEN (FromDate >  @fdate) THEN FromDate ELSE  @fdate End , CASE WHEN (ToDate  <  @tdate) THEN ToDate ELSE  @tdate End) AS 'Days'    FROM #data

Posted 1-Aug-14 2:29am

PrakashCs.net

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-08-03T21:22:00

Try this:

SQL

DECLARE @Seasons TABLE(SeasonID INT IDENTITY(1,1), SeasonName VARCHAR(30), FromDate DATETIME, ToDate DATETIME)


INSERT INTO @Seasons(SeasonName, FromDate, ToDate)
VALUES('SEASON1', '2014-01-01', '2014-04-30'),
('SEASON2', '2014-05-01', '2014-08-31'),
('SEASON3', '2014-09-01', '2014-12-31'),
('SEASON4', '2015-01-01', '2015-04-30'),
('SEASON5', '2015-05-01', '2015-08-31'),
('SEASON6', '2015-09-01', '2015-12-31')

SELECT *
FROM @Seasons 

DECLARE @sDate DATETIME
DECLARE @eDate DATETIME

SET @sDate = '2014-01-15'
SET @eDate = '2014-12-20'

SELECT SeasonID, SeasonName, FromDate, ToDate, startDate, endDate, DATEDIFF(DAY,startDate,endDate) + 1 AS NoOfDays 
FROM (
	SELECT SeasonID, SeasonName, FromDate, ToDate,
		startDate = CASE
						WHEN FromDate<@sDate THEN @sDate
						WHEN FromDate>=@sDate THEN FromDate
					END,
		endDate = CASE
					WHEN ToDate <= @eDate  THEN ToDate 
					WHEN ToDate>@eDate THEN @eDate
				END
	FROM @Seasons 
) AS T
WHERE DATEDIFF(DAY,startDate,endDate)>0