2 Replies Latest reply: Apr 13, 2016 4:19 AM by Marcus Sommer RSS

    Loading data using greater than date

    David Kwiatkowski

      I'm having trouble loading data using a date where clause. My date field is DT_OF_PYMT. My date format is 7/24/2014  12:00:00 AM. My current load is

       

      ODBC CONNECT TO [ASPEN QV Prod];

      SQL SELECT *

      FROM "SPD_RPT".ADMIN."GNRL_PYMT_DTL_FACT";

       

       

      SPECIALTY:

      LOAD LOV_CD AS PHY_SPECIALITY,

          LOV_DESC AS PHY_SPECIALTY_NAME

      FROM

      [\\ussbfscls06\kvdq645$\Compliance\Open Payments Data Analytics\QlikView\Static Data Sources\SpecialtyCode Values 10-20-2015.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

      I only want to see data for this current year (2016). I've tried multiple formats and get an error each time. Here is what I've tried:

       

      DBC CONNECT TO [ASPEN QV Prod];

      SQL SELECT *

      FROM "SPD_RPT".ADMIN."GNRL_PYMT_DTL_FACT"

      Where Date(DT_OF_PYMT,'DD-MM-YYYY hh:mm:ss') > '01-01-2016 12:00:00';

       

      SPECIALTY:

      LOAD LOV_CD AS PHY_SPECIALITY,

          LOV_DESC AS PHY_SPECIALTY_NAME

      FROM

      [\\ussbfscls06\kvdq645$\Compliance\Open Payments Data Analytics\QlikView\Static Data Sources\SpecialtyCode Values 10-20-2015.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

      My error Message:

      SQL##f - SqlState: 42S02, ErrorCode: 29, ErrorMsg: ERROR:  Function 'DATE(TIMESTAMP, UNKNOWN)' does not exist

        Unable to identify a function that satisfies the given argument types

        You may need to add explicit typecasts

      SQL SELECT *

      FROM "SPD_RPT".ADMIN."GNRL_PYMT_DTL_FACT"

      Where Date(DT_OF_PYMT,'DD/MM/YYYY hh:mm:ss') > '01/01/2016 12:00:00'

       

      Tried this:

      Where Date(Date#(DT_OF_PYMT,'MM/DD/YYYY hh:mm:ss')) > Date(Date#('1/1/2016 00:00:00', 'MM/DD/YYYY hh:mm:ss'));

      Got this error:

      SQL##f - SqlState: 42000, ErrorCode: 27, ErrorMsg: ERROR:  'SELECT * FROM "SPD_RPT".ADMIN."GNRL_PYMT_DTL_FACT" Where Date(Date#(DT_OF_PYMT,'MM/DD/YYYY hh:mm:ss')) > Date(Date#('1/1/2016 00:00:00', 'MM/DD/YYYY hh:mm:ss'))'

      error                                                                                                         ^ found ")" (at char 102) expecting `IN' or `NOT' or `OVERLAPS' or `Op' or `CONCAT_OP'

      SQL SELECT *

      FROM "SPD_RPT".ADMIN."GNRL_PYMT_DTL_FACT"

      Where Date(Date#(DT_OF_PYMT,'MM/DD/YYYY hh:mm:ss')) > Date(Date#('1/1/2016 00:00:00', 'MM/DD/YYYY hh:mm:ss'))