Embedded SQL Help with CURRENT_DATE
I am trying to create a task that has a variable property that a user can specify DaysBack but it keeps coming up blank when I ask the system to get the date as of DaysBack from the CURRENT_DATE. Can someone tell me why I am not getting a value back in my tDate variable?
Class OSU.Workday.TerminationsTask Extends %SYS.Task.Definition
{
Parameter TaskName As STRING = "OSU - Workday Termination Update";
Property DaysBack As %Integer [ InitialExpression = 1 ];
Method OnTask() As %Status
{
Set tSC=$$$OK
#Dim tException as %Exception.SystemException
try{
set tDate = ""
&sql(SELECT DATEADD('day', -:daysBack, CURRENT_DATE) INTO :tDate)
ZWRITE tDate
set tSC = ..SelectTerminations(tDate)
$$$ThrowOnError(tSC)
}Catch (tException) {
Set tSC = tException.AsStatus()
}
Return tSC
}
Method SelectTerminations(pDate As %DateTime) As %Status
{
Set tSC=$$$OK
#Dim ex as %Exception.SystemException
try
{
set sqlQuery = "SELECT EMPLID, MedCtrID, LastName, FirstName, EFFDT FROM OSU_Workday_SQL.PDMTerminations WHERE EFFDT > ?)"
set tStatment = ##class(%SQL.Statement).%New()
Set qStatus = tStatment.%Prepare(sqlQuery)
$$$ThrowOnError(qStatus)
set rset = tStatment.%Execute(pDate)
$$$ThrowOnError(rset)
#Dim tTermService As OSU.DataSource.Workday.TermService
set tTermConfigName="OSU.DataSource.Workday.TermService"
$$$ThrowOnError(##class(Ens.Director).CreateBusinessService(tEIDEMPIDConfigName,.tEIDEMPIDService))
if rset.%ROWCOUNT > 0
{
set tTerm = ##class(OSU.Workday.Messages.WorkdayTerminations).%New()
while rset.%Next()
{
set tTerm.EMPLID = rset.%Get("EMPLID")
set tTerm.MedCtrID = rset.%Get("MedCtrID")
set tTerm.Lastname = rset.%Get("LastName")
set tTerm.Firstname = rset.%Get("FirstName")
set tTerm.EFFDT = rset.%Get("EFFDT")
set tSC = tTerm.%Save()
$$$ThrowOnError(tSC)
set (tTermOutput,tHint) = ""
$$$ThrowOnError(tTermService.OnProcessInput(tTerm,.tTermOutput,.tHint))
}
}
}
Catch ex
{
Set tSC = ex.AsStatus()
}
Return tSC
}
ClassMethod TestTask() As %Status
{
Set tSC=$$$OK
set tTask = ##class(OSU.Workday.TerminationsTask).%New()
$$$ThrowOnError(tTask.OnTask())
Return tSC
}
/// Location and Revision of this file in Perforce (Auto-updating)
Parameter SrcVer = "$Id: //custom_ccrs/us/OSUM/OSUMHSCUSTOM/UAT/cls/OSU/Workday/TerminationsTask.xml#3 $";
}
Comments
To return values using the CURRENT_DATE function with embedded SQL in InterSystems, ensure the following:
-
Proper Syntax for SQL Statement: The
DATEADDfunction works correctly if the syntax follows SQL standards. It adjusts the date properly when values likeCURRENT_DATEand offsets (daysBack) are used. For example:SELECT DATEADD('day', -:daysBack, CURRENT_DATE) INTO :tDateThis structure is valid if the variables and SQL environment are set properly.
CURRENT_DATEtakes no arguments and returns the local date formatted as aDATEtype 1. -
Variable Passing & Bind Parameters: Ensure that the variable
daysBackis properly passed and referenced in its bind parameter, as defined by the colon preceding its name (:daysBack). The embedded SQL correctly integrates such bind parameters into queries 21. -
Date Format Handling:
CURRENT_DATEworks in the locale-specific display format or in$HOROLOGformat in logical mode. Depending on how the result is expected to be used or displayed, you may need to explicitly convert its type and format if necessary using theTO_DATEorCONVERTfunctions 1.
Make sure that you are initializing daysBack correctly and that its value is passed as expected within the embedded SQL code. If the above steps fail to address the issue, verify the SQL environment configuration and variable handling in the broader context of InterSystems ObjectScript programming.
Sources:
??
set daysBack=-1*daysBack
&sql(SELECT DATEADD('day', :daysBack, CURRENT_DATE) INTO :tDate)or (didn't try)
&sql(SELECT DATEADD('day', (-1)*:daysBack, CURRENT_DATE) INTO :tDate)I found my answer it was daysBack vs DaysBack