Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2013
    Posts
    2

    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,

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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

  3. #3
    Join Date
    Jan 2013
    Posts
    2

    Thanks tonkuma it works for me

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

    Thanks,

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •