CODEWITHSUNDEEP
sqlsql-server
Mayank
Mayank

Reputation: 393

Check date range from From and To Date

I have a sql table 

ProductId   Name    FromDate    To Date
1           A       1-Jan 2017       10-Jan 2017
2           B       5-Feb 2017       5-Feb 2017

Whenver I am inserting a new record in grid I have to check whether record for selected range date exist or not for that particular name

For example for Name B if new record comes daterange(5 Jan 2017 to 15 Jan 2017) as 5th jan -10th jan already exist , it should return false or product id.

Upvotes: 0

Views: 134

Answers (2)

S.Ali
S.Ali

Reputation: 29

'inputDate' can be applied to both from and to date. In this way, range can be verified:

select t.productId from table t
where inputDate between t.FromDate and t.toDate
and t.Name = 'B'

In your sproc add a check like this:

declare @pid int = (
    select t.productId from table t
    where inputDate between t.FromDate and t.toDate
    and t.Name = @name)
if @pid is null
    return cast(0 as bool)
else
    return @pid

Upvotes: 1

Abdul Rasheed
Abdul Rasheed

Reputation: 6729

Try the below script, this will return the ProductId if there is any conflict / overlap with the existing date range 

DECLARE @Input_From DATE --your from date here
        ,@Input_To  DATE --your to date here

SELECT  ProductId
FROM    Product_Table
WHERE   Name = 'B'
    AND (   @Input_From BETWEEN FromDate    AND ToDate
        OR  @Input_To   BETWEEN FromDate    AND ToDate
        OR  FromDate    BETWEEN @Input_From AND @Input_To
    )

Upvotes: 0

Related Questions