How do I find confirmed appointments greater than today's date.

Question

I have tried a number of different date codes, but don't have enough experience!  Here is my query, with the latest:

 SELECT "AppointmentStatus"."Description", "vPatientAppointments"."ApptStart", "vPatientProfile"."patientid"

 FROM   ("CentricityPS"."dbo"."vPatientProfile" "vPatientProfile" INNER JOIN "CentricityPS"."dbo"."vPatientAppointments" "vPatientAppointments" ON "vPatientProfile"."pid"="vPatientAppointments"."PId") INNER JOIN "CentricityPS"."dbo"."MedLists" "AppointmentStatus" ON "vPatientAppointments"."ApptStatusMId"="AppointmentStatus"."MedListsId"

 WHERE  "AppointmentStatus"."Description" LIKE 'Confirmed%' AND "vPatientAppointments"."ApptStart">= qie.getSystemDate()

 

I think I need to create a date function with the format, but.... I could really use some help.   

 

Here's my error:

line: 1] NoResultsException: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot find either column "qie" or the user-defined function or aggregate "qie.getSystemDate", or the name is ambiguous.

Query: SELECT "AppointmentStatus"."Description", "vPatientAppointments"."ApptStart", "vPatientProfile"."patientid"

FROM ("CentricityPS"."dbo"."vPatientProfile" "vPatientProfile" INNER JOIN "CentricityPS"."dbo"."vPatientAppointments" "vPatientAppointments" ON "vPatientProfile"."pid"="vPatientAppointments"."PId") INNER JOIN "CentricityPS"."dbo"."MedLists" "AppointmentStatus" ON "vPatientAppointments"."ApptStatusMId"="AppointmentStatus"."MedListsId"

WHERE "AppointmentStatus"."Description" LIKE 'Confirmed%' AND "vPatientAppointments"."ApptStart">=qie.getSystemDate() in <Unknown Source> at line number 1

 

Thank you!

dh

 

Answer 1

You can use the following node tag within a query string to replace it with the current formatted date:

{SYSTEM_DATE[yyyy-MM-dd]}

The following KB outlines several ways of comparing dates within a query.

https://www.qvera.com/kb/index.php/28/compare-dates-query-without-time-records-date-ignoring-time?show=28#q28