ShortQuery help needed

[osjaved]

I am using PUT ShortQuery: https://api.opendental.com/api/v1/queries/ShortQuery

The following query does not return any results beyond 2020-09-26:
"SqlCommand": "SET @FromDate= '2020-04-29', @ToDate='2026-04-31'; select a.AptDateTime, p.FName, p.WirelessPhone FROM appointment a INNER JOIN patient p ON p.PatNum=a.PatNum WHERE a.AptDateTime BETWEEN @FromDate AND @ToDate"

I need it to return data in future i.e. pull patients with upcoming appointments (and not in the past)

So this does not return any results although there are appointments on 4/29/2026
"SqlCommand": "SET @FromDate= '2026-04-29', @ToDate='2026-04-31'; select a.AptDateTime, p.FName, p.WirelessPhone FROM appointment a INNER JOIN patient p ON p.PatNum=a.PatNum WHERE a.AptDateTime BETWEEN @FromDate AND @ToDate"

Can someone help with that please?

Thanks

[Tom Zaccaria]

Use this in place of your from and to dates and just change the number of days you want to search past and present.

drtmz

(DATE(a.AptDateTime) BETWEEN curdate() AND curdate() +interval 10 day)
OR
(DATE(a.AptDateTime) BETWEEN curdate() AND curdate() -interval 10 day)

[osjaved]

I tried that. It still does not pull appointments and patients (although there is an appointment on 4/29)

OD.jpg (149.13 KiB) Viewed 1807 times

[Tom Zaccaria]

Take 2:

select a.AptDateTime, p.FName, p.WirelessPhone
FROM appointment a
INNER JOIN patient p ON p.PatNum=a.PatNum WHERE
(DATE(a.AptDateTime)
BETWEEN Curdate()+Interval 2 Day AND Curdate()+Interval 2 Day) AND a.AptStatus IN (1,2,4)

You will have to manipulate the interval days.

drtmz

[justine]

I am using PUT ShortQuery: https://api.opendental.com/api/v1/queries/ShortQuery

The following query does not return any results beyond 2020-09-26:
"SqlCommand": "SET @FromDate= '2020-04-29', @ToDate='2026-04-31'; select a.AptDateTime, p.FName, p.WirelessPhone FROM appointment a INNER JOIN patient p ON p.PatNum=a.PatNum WHERE a.AptDateTime BETWEEN @FromDate AND @ToDate"

I need it to return data in future i.e. pull patients with upcoming appointments (and not in the past)

So this does not return any results although there are appointments on 4/29/2026
"SqlCommand": "SET @FromDate= '2026-04-29', @ToDate='2026-04-31'; select a.AptDateTime, p.FName, p.WirelessPhone FROM appointment a INNER JOIN patient p ON p.PatNum=a.PatNum WHERE a.AptDateTime BETWEEN @FromDate AND @ToDate"

Can someone help with that please?

Thanks

Hello osjaved,

Your query is likely invalid because April 31st isn't a real day.

You could try something like this:

Code: Select all

SELECT
  a.AptDateTime,
  p.FName,
  p.WirelessPhone
FROM appointment a
INNER JOIN patient p
  ON p.PatNum = a.PatNum
WHERE a.AptDateTime >= '2026-04-29 00:00:00'
  AND a.AptDateTime <  '2026-04-30 00:00:00'
ORDER BY a.AptDateTime;

Thanks!

[osjaved]

Tried this one and still getting the same result

OD2.jpg (194.68 KiB) Viewed 1532 times

[osjaved]

Tried this one but still can't get results

Take 2:

select a.AptDateTime, p.FName, p.WirelessPhone
FROM appointment a
INNER JOIN patient p ON p.PatNum=a.PatNum WHERE
(DATE(a.AptDateTime)
BETWEEN Curdate()+Interval 2 Day AND Curdate()+Interval 2 Day) AND a.AptStatus IN (1,2,4)

You will have to manipulate the interval days.

drtmz

[RyanH]

Tried this one and still getting the same result
OD2.jpg

Hello osjaved,

Have you double checked that the authorization header is the same on both requests? It looks like the /appointments GET request has 8 headers, while the /queries/ShortQuery PUT request has 10 headers. After confirming that, can you test the result from the following command?

Code: Select all

SELECT AptDateTime from appointment WHERE AptNum=279;

[osjaved]

Yes it was the incorrect header that was causing it. It works now. Thank you!