SQL Year function not working for dynamic attachments in Print and Delivery

Paul Woods

• September 20, 2024 13:03

In the report action for Emailing we are trying to attach a document that is relevant to the invoice being sent. The mapping for the location of the file to attach is constructed using the year and month of the invoice date. For example an invoice with date of 12/08/2024 would have a matching picking list pdf in the following folder:

\\b1_shr\attachements\picklists\2024\August\

The 2024 comes from the year of the invoice and August from the month. We have created the following SQL statement to generate dynamically the folder location:

SQL:Select '\\b1_shr\attachements\picklists\'+cast(Year($[$10.0.date]) as char(4))+'\'+CHOOSE(month($[OINV.DOcDate.date]),'January','February','March','April','May','June','July','August','September','October','November','December')+'\P-'+cast($[OINV.DocNum.0] as varchar(10))+'.pdf'

It returns 

\\b1_shr\attachements\picklists\1905\July\P-202453289.pdf

The Year is always returning 1905 and the month is wrong as well. 

 

Any ideas?

Vote

0

• 

  Alessandro Lopez Santinelli

  • September 23, 2024 19:45

  Hi Paul, try this:

  Select '\\b1_shr\attachements\picklists\'+SUBSTRING(CONVERT(varchar, docdate, 112), 1, 4)+'\'+CHOOSE(month(DocDate),'January','February','March','April','May','June','July','August','September','October','November','December')+'\P-'+cast(docnum as varchar)+'.pdf'from oinv where DocEntry=DocKey@

  regards

   

Please sign in to leave a comment.