Forum Discussion

Nathan_H_'s avatar
Nathan_H_
Ideator I
3 years ago
Solved

C# SQL Date Query

Good day all, I am trying to create a dynamic report to produce the time it take between starting an activity and ending an activity.  I can get the information I am looking for by using SQL, but i...
Reporting
SQL
  • AngelaIp's avatar
    AngelaIp
    3 years ago

    Edit: We are all blind! 

    Take a look at my picture again. Do you notice something? I have added your BETWEEN date filter. Does the filter work? Yes...but NO! It´s uses the filter but with the date in a in wrong format, which can lead to all kind of wrong results. [I cannot remember ever having similar problems before whenever using dates....]

    Solution in my case: WHERE  (Act_Assign.MODIFIED_ON BETWEEN '20230801' AND '20230802')

    As alternative, something with convert(datetime,...) should also do the job. 

AngelaIp's avatar
AngelaIp
Ideator I
3 years ago

Does the query deliver correct result in SSMS?

Do you get too much results, or no results at all?

If you get no results, your date format is probably not recognized by Innovator and you need to convert it:

 link 

If you get too much results, check your WHERE query. You have two filters for name. But the second maybe connects to the AND for the date, so you maybe need more brackets.

  • Nathan_H_'s avatar
    Nathan_H_
    Ideator I
    3 years ago

    Angela,

    I do get results.  The results indicate that the Act_Assign.modified_on BETWEEN '2023-08-01' AND '2023-08-02' is being ignored and everything is returned.  If I try to put the date in a format of mm/dd/yy the system will give an error of incorrect date format.  I agree that the mm/dd/yy format is stupid.  The filters for the name are to limit the returns during testing.  The end product will not have those filters.  What is SSMS?  Thanks.

    • AngelaIp's avatar
      AngelaIp
      Ideator I
      3 years ago

      SSMS ist just the MS SQL Server Management Studio. I assumed you used it to build the query.

      Out of interest I made a quick test of the query. I had to remove one table that I don´t have, but the rest worked (in SSMS).

      Is your ORDER BY part also ignored?

      • Nathan_H_'s avatar
        Nathan_H_
        Ideator I
        3 years ago

        I don't have access to our SQL server.  The SQL code is in a C# method.