Date Parameter to Cache SQL

0
votes

I am very new to Cache. I am trying to develop a report with date parameters. When I issue the SQL command:

SELECT TOP 2 ad.admission_date from system.admission ad WHERE convert(sql_date,ad.admission_date) >= convert(sql_date,'08-01-2014' )

I get what I expect two records. One of which is 10/1/2010 12:00:00 AM. Then if I issue the command 

SELECT TOP 2 ad.admission_date from system.admission ad WHERE convert(sql_date,ad.admission_date) <= convert(sql_date,'08-01-2014' )

I get no values returned?

When I issue the command 

SELECT TOP 2 {fn convert('10-03-2010', sql_date) } FROM system.admission_data

I get two NULL values. Clearly I am confused about how Cache works.

2
intersystems-cache

2 Answers

1
votes

I have found that if you use the standard ODBC format (yyyy-MM-dd) for the date you don't need to use the convert and it is much more efficient:

WHERE ad.admission_date <= '2014-08-01'

0
votes

I formated date incorrectly. I have my code working now. Should look something like select top 2 convert(DATE, '10/03/2010 12:00:00 AM') .... and then I can actually do comparisons.