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

Paul Woods

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?

Comentarios

1 comentario

Fecha Votos
  • Comment author
    Alessandro Lopez Santinelli

    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

     

    0

Iniciar sesión para dejar un comentario.