Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'))
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';
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';
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