Loading data using greater than date

[#1]

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'))

[Gysbert_Wassenaar · Accepted solution]

Your SQL Server doesn't speak Qlikview script. So don't use Qlikview function in sql statements that your SQL Server needs to understand. Try this:

DBC CONNECT TO [ASPEN QV Prod];

SQL SELECT *

FROM "SPD_RPT".ADMIN."GNRL_PYMT_DTL_FACT"

Where DT_OF_PYMT>= '01/01/2016';

[marcus_sommer]

Your used date or date# functions are qlikview-functions and couldn't be used within the sql-statement. If your load is rather small you could transfer these filtering to the qlikview side maybe within a resident load or more commonly used a Preceding Load.

In your case might it be easier to use:

...

Where year(DT_OF_PYMT) >= 2016;

then I think year would be available within your database and your used odbc/oledb-driver.

- Marcus