none
SQLDependency DateRange RRS feed

  • Question

  • Hello all,

    I'm having a problem using the sqldependency and trying to query a date / daterange.

    When i put the text of the command to

    @"Select ID, VisitStateID from dbo.Visit where ID between 2 and 30"

    and execute I have no problems.  Everything works correctly, I get the notifications when i chage the visitstateid in the db.  However if i change the where clause to:

    @"Select ID, VisitStateID from dbo.Visit where (ScheduledStartDate>='10/30/2007 00:00:00' and ScheduledEndDate<='10/30/2007 23:59:59')"; I recieve:

        Info: Invalid
        Source: Statement
        Type: Subscribe

    in the OnChange event when the command is executed. 

    I've referenced:
    http://msdn2.microsoft.com/en-us/library/aewzkxxh.aspx but don't see anything I am doing incorrectly.  Am I missing something?

    Thanks in advance,
    Tom

    Monday, November 19, 2007 9:24 AM

Answers

All replies

  • There are fixed rules for what you can do with SQL Server DateTime formats and the operator you are using is dependent on ANSI SQL NULL settings of your database.  So I think should use the version that gives you correct results because the WHERE clause is not aware of NULLs.

     

    http://www.karaszi.com/SQLServer/info_datetime.asp 

     

    http://msdn2.microsoft.com/en-us/library/ms181567.aspx

    Wednesday, November 21, 2007 9:00 PM
  •  

    Hi Caddre,

     

    Thanks for your reply.  I tried all the different combinations of DateTime I could think of with no luck.  Has anyone had any success with the SQLDependency and a date range?  Is it perhaps a setting in SQL server itself?  Like I said in the original post I can get it work with ints, just not dates.

     

    Thanks in advance,

    Tom

    Friday, November 30, 2007 8:03 PM