I have to find out DATE from start date & end date

Question

I have to find out DATE from start date & end date

ranganathanmca

SSC Eights!

Points: 920
More actions
August 31, 2011 at 1:01 am

#244090

Hi Guys.,
I have to find out DATE from start date & end date,i am getting input from the user date i need to find out if start date & end date in between date is there are not
Example date
start Date End Date
08/01/2011 08/10/2011
08/15/2011 08/25/2011
Please guide to reslove this problem
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Could you elaborate a little more? I am not sure I quite understand what you need.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ranganathanmca SSC Eights! Points: 920 More actions · Answer 2

Thanks for Reply SQLRNNR,

Table Data:

Startdate Enddate

08/01/2011 08/12/2011

how to find in between dates to the start date & enddate , User will enter only one date

Thanks

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Something like this?

DECLARE @UserDate datetime

SET @UserDate = '20110810'

DECLARE @SampleData TABLE (

Startdate datetime,

Enddate datetime

)

INSERT INTO @SampleData VALUES ('20110801', '20110812')

SELECT *

FROM @SampleData

WHERE @UserDate BETWEEN StartDate AND EndDate

-- Gianluca Sartori

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

I've interpreted the problem differently to Gianluca, so there's a good chance that this doesn't do what you want, but feel free to check 🙂

DECLARE @UserDate datetime

SET @UserDate = '20110810'

DECLARE @SampleData TABLE (

Startdate datetime,

Enddate datetime

)

INSERT INTO @SampleData VALUES ('20110801', '20110812')

--Tally table would be better, for testing purposes I've included one here "on the fly"

;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),

t2 AS (SELECT 1 N FROM t1 x, t1 y),

t3 AS (SELECT 1 N FROM t2 x, t2 y),

t4 AS (SELECT 1 N FROM t3 x, t3 y),

tally AS (SELECT DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,0) AS betweendata

FROM t4 x, t4 y)

--Actual Code

SELECT CASE WHEN a.isBetween > 0

THEN 'Yes'

ELSE 'No' END AS isDateBetween

FROM (SELECT COUNT(*) AS isBetween FROM @SampleData

CROSS APPLY tally

WHERE Startdate <= betweendata AND Enddate >= betweendata

AND betweendata = @UserDate) a

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Aiello DBA Right there with Babe Points: 784 More actions · Answer 5

I enter this post thinking to write, but gianluca was clean enogth 🙂