OLEDB Not reading my dates correctly

[Anonymous]

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'

[andydietler]

Select *

From T

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

;

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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

[Anonymous]

Getting hung up here...

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

   THEN 'Open'

[sunny_talwar · Accepted solution]

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'