Post reply

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

  • 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

  • August 31, 2011 at 1:14 am

    #1375321

    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

  • August 31, 2011 at 1:25 am

    #1375328

    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

  • August 31, 2011 at 1:35 am

    #1375334

    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

  • August 31, 2011 at 2:10 am

    #1375341

    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/

  • August 31, 2011 at 8:20 am

    #1375503

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

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

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