Check Datetime Existence within multiple columns

  • Hi there,

    I've a BillDate as date, and a Mark as bit column in First table.

    (Mark=0 by default)

    In Second table I've FromDate as date, and ToDate as date Column.

    I want to set Mark=1 if BillDate is exists between FromDate & ToDate

    Let Say In First Table the data is

    ----------------------------

    BillDate | Mark

    ----------------------------

    2012-11-10 11:15:30 | 0

    2012-12-12 09:00:00 | 0

    In Second Table the data is

    ---------------------------------------------

    FromDate | ToDate

    ---------------------------------------------

    2012-11-01 07:00:00 | 2012-11-09 23:59:59

    2012-12-08 07:00:00 | 2012-12-15 23:59:59

    So in the above scenario only the second row from First table

    which is having, BillDate->2012-12-12 09:00:00 will be Mark as 1

    because it comes between second row of second table

    I hope I've explained my scenario,

  • Please take a moment to read through the second link in my signature on posting code and data for the best help.

    But based on your post, you need something like this

    [cod="sql"]

    DECLARE @bill TABLE (BillDate DATETIME, Mark BIT)

    INSERT INTO @bill VALUES

    ('2012-11-10 11:15:30',0),

    ('2012-12-12 09:00:00',0)

    DECLARE @Dates TABLE (FromDate DATETIME, ToDate DATETIME)

    INSERT INTO @Dates VALUES

    ('2012-11-01 07:00:00','2012-11-09 23:59:59'),

    ('2012-12-08 07:00:00','2012-12-15 23:59:59')

    UPDATE

    B

    SET

    Mark = 1

    FROM

    @bill B

    INNER JOIN

    @Dates d

    ON

    b.BillDate >= d.FromDate

    AND

    b.BillDate <= d.ToDate

    SELECT * FROM @bill

    [/code]

  • Thank you so much Tunnel.

    I've tried it and its working fine.

    How can I mark your post as an answer?

  • You cannot mark them as answered on SSC, as it is primarily a discussion forum so topics are open for all to see and people may come back with their own take on how to solve your problem.

Viewing 4 posts - 1 through 3 (of 3 total)

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