Check Datetime Existence within multiple columns

adnan8t2 · #1 (**permalink**) 01-23-13, 07:17

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,

tonkuma · #2 (**permalink**) 01-23-13, 15:20

Date Time calculaions are quite different on each DBMS.
So, it would be better to ask your question on specific product forum rather than on general ANSI SQL forum.

Try this by adjusting to your specific DBMS syntax(if neccesary)

Code:

Mimer SQL-2003 Validator

Check your SQL against the SQL-2003 standard using the Mimer SQL Validator 2003. 

The SQL-2003 Validator!

Enter your SQL statement(s) in the box below and simply click the "Test SQL" button: 

UPDATE First_Table AS f
   SET Mark = 1
 WHERE EXISTS
       (SELECT 0
         FROM  Second_Table AS s
         WHERE f.Bill_Date
               BETWEEN s.From_Date AND s.To_Date
       ) 


Result:

Conforms to Core SQL-2003

adnan8t2 · #3 (**permalink**) 01-24-13, 05:20

I've tried your given solution and its working fine.

Thanks,

Internet Services

Web Development

Digital Marketing

Consumer Tech