topic Re: OLEDB Not reading my dates correctly in QlikView

OLEDB Not reading my dates correctly

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a oracle query, see below. When I load this in QV using the SQL comment it return no results. When I run on the client I get results. I want to be able to update this query where it works in QV SQL command and on the client. Thoughts?

SQL

select *

from T

where enddate = '01-JAN-3000'

Re: OLEDB Not reading my dates correctly

andydietler — Thu, 30 Jun 2016 21:28:12 GMT

Select *

From T

WHERE Date(EndDate) = Date('01/01/3000')

;

Re: OLEDB Not reading my dates correctly

Anonymous — Thu, 30 Jun 2016 21:41:12 GMT

Using this query in the QV SQL command for a OLE DB connection. The above does not work

Re: OLEDB Not reading my dates correctly

sunny_talwar — Thu, 30 Jun 2016 21:53:11 GMT

Are you sure the year is 3000 and not something else? Also can you try this:

SQL

select *

from T

where enddate = '01-Jan-3000';

Re: OLEDB Not reading my dates correctly

Anonymous — Thu, 30 Jun 2016 22:05:28 GMT

see code below

SELECT DISTINCT

CASE WHEN x.END_dt = '01-JAN-3000' AND x.REASON IS NULL

THEN 'Open'

WHEN x.END_dt IS NOT NULL OR x.REASON IS NOT NULL

THEN 'Closed'

ELSE 'Unexpected x.END_dt value: ' || to_char(x.END_dt)

END AS Status

FROM HIST x

WHERE 1=1

and rownum <10

--and to_char(x.end_dt,'MM/DD/YYYY') = '01/01/3000'

and x.end_dt = '01-JAN-3000'

order by x.end_dt desc

When I change to to_char(x.end_dt,'MM/DD/YYYY') = '01/01/3000' it return values but they are all status of closed. They are supposed to be status = Open

Re: OLEDB Not reading my dates correctly

Anonymous — Thu, 30 Jun 2016 22:16:37 GMT

Getting hung up here...

CASE WHEN x.END_dt = '01-JAN-3000' AND x.REASON IS NULL

THEN 'Open'

Re: OLEDB Not reading my dates correctly

sunny_talwar — Thu, 30 Jun 2016 22:19:12 GMT

Why are you using different syntaxes?

What about this:

CASE WHEN to_char(x.end_dt,'MM/DD/YYYY') = '01/01/3000' AND x.REASON IS NULL

THEN 'Open'