Uploaded image for project: 'i2b2 Core Software'
  1. i2b2 Core Software
  2. CORE-209

CURRENT_DATE Keyword on MS SQL Server

Details

    • Bug
    • Status: Closed
    • Blocker
    • Resolution: Cannot Reproduce
    • 1.7.07
    • None
    • CRC Cell
    • None
    • Analysis View
    • i2b2 Core

    Description

      The SQL Keyword "CURRENT_DATE" is not a function on MSSQL Server. Use GETDATE() function instead.

      currently wrongly used:

      function: analyses timeline

      Log:
      [INFO] Using datasource java:/QueryToolDemoDS

      select p.patient_num from i2b2demodata.patient_dimension p where p.patient_num IN (select patient_num from i2b2demodata.patient_dimension where birth_date > (CURRENT_DATE - INTERVAL '3653.5 day'))
       group by p.patient_num )



      Attachments

        Activity

          not a software bug - the CURRENT_DATE is used in the Demo Data, Table I2B2 (C_DIMCODE).
          Proposed: decrease the impact level.
          tobiasgrossmann Tobias Grossmann added a comment - not a software bug - the CURRENT_DATE is used in the Demo Data, Table I2B2 (C_DIMCODE). Proposed: decrease the impact level.
          mem61 Mike Mendis added a comment -
          The demo data has three different files for the three Databases (Oracle, Sql Server and Postgre) Just to verify, in your db.properties the first line did it say db.type=oracle or db.type=sqlserver. It should be db.type=sqlserver.
          mem61 Mike Mendis added a comment - The demo data has three different files for the three Databases (Oracle, Sql Server and Postgre) Just to verify, in your db.properties the first line did it say db.type=oracle or db.type=sqlserver. It should be db.type=sqlserver.
          properties are correctly set.
          tobiasgrossmann Tobias Grossmann added a comment - properties are correctly set.
          but don't see a "link" in the i2b2 table to the DB Type of each statement?
          tobiasgrossmann Tobias Grossmann added a comment - but don't see a "link" in the i2b2 table to the DB Type of each statement?
          mem61 Mike Mendis added a comment -
          The (CURRENT_DATE - INTERVAL '3653.5 day')) that is being added is coming from the ontology. This statement would find patients who are 10 years old from today. In the ontology it is in the c_dimcode.

          I did a grep on the
              edu.harvard.i2b2.data/Release_1-7/NewInstall/Metadata/demo/scripts/[postgres, oracle, sqlserver] folder and only found current_date in the postgres. So I am confused where it is coming from.

          Would it be possible to run the following select statement on the metadata
          select * from i2b2 where c_fullname like '\i2b2\Demographics\Age\%'
           and tell me what the c_dimcode looks like.
          mem61 Mike Mendis added a comment - The (CURRENT_DATE - INTERVAL '3653.5 day')) that is being added is coming from the ontology. This statement would find patients who are 10 years old from today. In the ontology it is in the c_dimcode. I did a grep on the     edu.harvard.i2b2.data/Release_1-7/NewInstall/Metadata/demo/scripts/[postgres, oracle, sqlserver] folder and only found current_date in the postgres. So I am confused where it is coming from. Would it be possible to run the following select statement on the metadata select * from i2b2 where c_fullname like '\i2b2\Demographics\Age\%'  and tell me what the c_dimcode looks like.
          Hi Mike, then please close the Bug - I would guess that we somehow managed to screw up the installation / installation scripts then? As I've the same grep results.

          And having the CURRENT_DATE in the C_DIMCODE. So somehow wrong insert scripts had been used?
          tobiasgrossmann Tobias Grossmann added a comment - Hi Mike, then please close the Bug - I would guess that we somehow managed to screw up the installation / installation scripts then? As I've the same grep results. And having the CURRENT_DATE in the C_DIMCODE. So somehow wrong insert scripts had been used?
          mem61 Mike Mendis added a comment -
          somehow it appears the postgres got inserted, maybe postgres was used on the db.type by accident. I will close this one, but if you find that it really is a i2b2 issue, please open a new one or reopen this. thanks
          mem61 Mike Mendis added a comment - somehow it appears the postgres got inserted, maybe postgres was used on the db.type by accident. I will close this one, but if you find that it really is a i2b2 issue, please open a new one or reopen this. thanks

          People

            mem61 Mike Mendis
            tobiasgrossmann Tobias Grossmann
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: