Setting a Batch posting date based on a source field
Debbie Knoebl asked 5 years ago
I am trying to create a task that will set the batch date on a payables transaction to the end of the month of the document date in the source file. I can get my script to work if I hard code in the date, but when I try to use the SQL function EOM and my source field, the task fails. Is there any way I can get this to work?
This is what my query looks like:
update sy00500
set glpostdt= EOM( _Doc_Date)
WHERE BACHNUMB=CONCAT( ‘_LocationCode’ ,’PP’, ‘EOM( _Doc_Date )’)
I’ve tried doing it as a script task type as well, but I can’t pull source fields into that.
Any ideas?
Thanks!
This is what my query looks like:
update sy00500
set glpostdt= EOM( _Doc_Date)
WHERE BACHNUMB=CONCAT( ‘_LocationCode’ ,’PP’, ‘EOM( _Doc_Date )’)
I’ve tried doing it as a script task type as well, but I can’t pull source fields into that.
Any ideas?
Thanks!
Answers
Patrick Roth Staff answered 5 years ago
Debbie,
your EOM() function might be custom or on a newer version of SQL as I don’t seem to have that in my SQL. So that is a possibility. On my SQL there is an EOMONTH() function which does that so I think you maybe have just the wrong name.
More to the point, you have issues that I can see right away.
1. on the setglposdt line
the _Doc_Date doesn’t have single quotes around it. that will 100% break the TSQL
set glpostdt = EOMONTH(‘_Doc_Date’)
2. same with the where BACHNUMB = line
the result of the concat needs single quotes around it. And also the same for the _doc_Date field.
declare @where varchar(255)
set @where = CONCAT(‘_LocationCode’,’PP’,EOMONTH(‘_DocDate’))
then use that variable we made
where BACHNUMB = @where
that looks right to me but I didn’t test.
Worse comes to worse, run a SQL Profile Trace so that you can see what this turns into in SQL
your EOM() function might be custom or on a newer version of SQL as I don’t seem to have that in my SQL. So that is a possibility. On my SQL there is an EOMONTH() function which does that so I think you maybe have just the wrong name.
More to the point, you have issues that I can see right away.
1. on the setglposdt line
the _Doc_Date doesn’t have single quotes around it. that will 100% break the TSQL
set glpostdt = EOMONTH(‘_Doc_Date’)
2. same with the where BACHNUMB = line
the result of the concat needs single quotes around it. And also the same for the _doc_Date field.
declare @where varchar(255)
set @where = CONCAT(‘_LocationCode’,’PP’,EOMONTH(‘_DocDate’))
then use that variable we made
where BACHNUMB = @where
that looks right to me but I didn’t test.
Worse comes to worse, run a SQL Profile Trace so that you can see what this turns into in SQL
Debbie Knoebl replied 5 years ago Thank you Patrick! I was just missing the quote around the doc date.