SQL Substrings

Substring values in SQL

The example below is an SQL function in Net.Data manipulating a date format and concatenating the date digits with edit characters for presentation.

Substring with CONCAT


%{=====================================================%}
%{============== Get Project Data =====================%}
%{=====================================================%}

%FUNCTION(DTW_SQL) SHOWPOS (OUT t1)  {

  SELECT PHVEND, PHSTK, PHORD#,
    substr(DIGITS(PHDT),5,2) concat '/' concat substr(DIGITS(PHDT),7,2) concat '/' concat substr(DIGITS(PHDT),3,2) as PODATE,
    substr(DIGITS(PHSPDT),5,2) concat '/' concat substr(DIGITS(PHSPDT),7,2) concat '/' concat substr(DIGITS(PHSPDT),3,2) as OSHDATE,
    substr(DIGITS(PHPMDTL),5,2) concat '/' concat substr(DIGITS(PHPMDTL),7,2) concat '/' concat substr(DIGITS(PHPMDTL),3,2) as LASTPRM,
    PHQTY, PHCOST, PHDUDT
    FROM PORHEDL5 INNER JOIN OPSCTLLF ON PHSTK = SYKYID1
    WHERE  PHVEND=$(VND)
      %{  And  PHSTK <> 'N' %}
	  And   (substr(SYDATA,1,1)='Y' Or substr(SYDATA,4,1)='Y')
    ORDER BY PHORD#

    %MESSAGE { 100 : { @Write_Return() %} : continue %}