Basta usar Substring e Convert que você consegue ver o resultado dos campos.
Para visualizar o usuário que incluiu ou alterou, você deve concatenar as seguintes posições: 03, 07, 11, 15, 19, 02, 06, 10, 14, 01, 18, 05, 09, 13, 17, 04, 08. Pois nas versões antigas do Protheus, era gravado o nome do usuário (por exemplo, “Daniel Atilio”), e a partir das versões mais novas, é gravado apenas o código (por exemplo, “000007”).
Para visualizar a data, você deve pegar a posição 12 e a 16, transformar em ascii tirando 50 caracteres e converter para data, pegando como início de corte 01/01/1996. Se for o campo de alteração, essa posição 12 além de tirar 50, você deve multiplicar por 100.
Abaixo o exemplo da query completa.
SELECT
B1_COD,
B1_DESC,
B1_USERLGI,
B1_USERLGA,
CASEWHENSUBSTRING(B1_USERLGI, 03, 1) != ' 'ANDB1_USERLGI != ''THEN
CONVERT(VARCHAR,DATEADD(DAY,CONVERT(INT,CONCAT(ASCII(SUBSTRING(B1_USERLGI,12,1)) - 50, ASCII(SUBSTRING(B1_USERLGI,16,1)) - 50) + IIF(SUBSTRING(B1_USERLGI,08,1) = '<',10000,0)),'1996-01-01'), 103)
ELSE
''
ENDASDATA_INCL,
CASEWHENSUBSTRING(B1_USERLGA, 03, 1) != ' 'ANDB1_USERLGA != ''THEN
CONVERT(VARCHAR,DATEADD(DAY,CONVERT(INT,CONCAT(ASCII(SUBSTRING(B1_USERLGA,12,1)) - 50, ASCII(SUBSTRING(B1_USERLGA,16,1)) - 50) + IIF(SUBSTRING(B1_USERLGA,08,1) = '<',10000,0)),'1996-01-01'), 103)
ELSE''
ENDASDATA_ALT,
LEFT(
SUBSTRING(B1_USERLGI, 11, 1) +
SUBSTRING(B1_USERLGI, 15, 1) +
SUBSTRING(B1_USERLGI, 19, 1) +
SUBSTRING(B1_USERLGI, 02, 1) +
SUBSTRING(B1_USERLGI, 06, 1) +
SUBSTRING(B1_USERLGI, 10, 1) +
SUBSTRING(B1_USERLGI, 14, 1) +
SUBSTRING(B1_USERLGI, 01, 1) +
SUBSTRING(B1_USERLGI, 18, 1) +
SUBSTRING(B1_USERLGI, 05, 1) +
SUBSTRING(B1_USERLGI, 09, 1) +
SUBSTRING(B1_USERLGI, 13, 1) +
SUBSTRING(B1_USERLGI, 17, 1) +
SUBSTRING(B1_USERLGI, 04, 1) +
SUBSTRING(B1_USERLGI, 08, 1)
, 6) ASUSER_INCL,
LEFT(
SUBSTRING(B1_USERLGA, 11, 1) +
SUBSTRING(B1_USERLGA, 15, 1) +
SUBSTRING(B1_USERLGA, 19, 1) +
SUBSTRING(B1_USERLGA, 02, 1) +
SUBSTRING(B1_USERLGA, 06, 1) +
SUBSTRING(B1_USERLGA, 10, 1) +
SUBSTRING(B1_USERLGA, 14, 1) +
SUBSTRING(B1_USERLGA, 01, 1) +
SUBSTRING(B1_USERLGA, 18, 1) +
SUBSTRING(B1_USERLGA, 05, 1) +
SUBSTRING(B1_USERLGA, 09, 1) +
SUBSTRING(B1_USERLGA, 13, 1) +
SUBSTRING(B1_USERLGA, 17, 1) +
SUBSTRING(B1_USERLGA, 04, 1) +
SUBSTRING(B1_USERLGA, 08, 1)
, 6) ASUSER_ALTE
FROM
SB1990 SB1
WHERE
B1_FILIAL = ' '
ANDB1_MSBLQL != '1'
ANDSB1.D_E_L_E_T_ = ' '
/* Exemplo original:
SELECT
B1_COD,
B1_USERLGI,
B1_USERLGA,
SUBSTRING(B1_USERLGI, 03, 1) +
SUBSTRING(B1_USERLGI, 07, 1) +
SUBSTRING(B1_USERLGI, 11, 1) +
SUBSTRING(B1_USERLGI, 15, 1) +
SUBSTRING(B1_USERLGI, 19, 1) +
SUBSTRING(B1_USERLGI, 02, 1) +
SUBSTRING(B1_USERLGI, 06, 1) +
SUBSTRING(B1_USERLGI, 10, 1) +
SUBSTRING(B1_USERLGI, 14, 1) +
SUBSTRING(B1_USERLGI, 01, 1) +
SUBSTRING(B1_USERLGI, 18, 1) +
SUBSTRING(B1_USERLGI, 05, 1) +
SUBSTRING(B1_USERLGI, 09, 1) +
SUBSTRING(B1_USERLGI, 13, 1) +
SUBSTRING(B1_USERLGI, 17, 1) +
SUBSTRING(B1_USERLGI, 04, 1) +
SUBSTRING(B1_USERLGI, 08, 1) AS USER_INC,
CONVERT(VARCHAR(10),CAST(DATEADD(DAY,CONVERT(INT,CONCAT(ASCII(SUBSTRING(B1_USERLGI,12,1)) - 50, ASCII(SUBSTRING(B1_USERLGI,16,1)) - 50)),'1996-01-01') AS DATETIME),103) AS DATA_INC,
SUBSTRING(B1_USERLGA, 03, 1) +
SUBSTRING(B1_USERLGA, 07, 1) +
SUBSTRING(B1_USERLGA, 11, 1) +
SUBSTRING(B1_USERLGA, 15, 1) +
SUBSTRING(B1_USERLGA, 19, 1) +
SUBSTRING(B1_USERLGA, 02, 1) +
SUBSTRING(B1_USERLGA, 06, 1) +
SUBSTRING(B1_USERLGA, 10, 1) +
SUBSTRING(B1_USERLGA, 14, 1) +
SUBSTRING(B1_USERLGA, 01, 1) +
SUBSTRING(B1_USERLGA, 18, 1) +
SUBSTRING(B1_USERLGA, 05, 1) +
SUBSTRING(B1_USERLGA, 09, 1) +
SUBSTRING(B1_USERLGA, 13, 1) +
SUBSTRING(B1_USERLGA, 17, 1) +
SUBSTRING(B1_USERLGA, 04, 1) +
SUBSTRING(B1_USERLGA, 08, 1) AS USER_ALT,
CASE WHEN SUBSTRING(B1_USERLGA, 03, 1) != ' ' THEN
CONVERT(VARCHAR,DATEADD(DAY,((ASCII(SUBSTRING(B1_USERLGA,12,1)) - 50) * 100 + (ASCII(SUBSTRING(B1_USERLGA,16,1)) - 50)),'19960101'),112)
ELSE ''
END AS DATA_ALT
FROM
SB1010 SB1
WHERE
B1_USERLGI != ' '
*/
Abaixo uma query, para quem tem o dicionário no banco, onde é possível fazer o relacionamento com a tabela SYS_USR:
SELECT
B1_COD,
B1_DESC,
B1_USERLGI,
B1_USERLGA,
USR_I.USR_ID AS USRINC_COD,
USR_I.USR_NOME AS USRINC_NOM,
CASE WHEN SUBSTRING(B1_USERLGI, 03, 1) != ' 'AND B1_USERLGI != ''THEN
CONVERT(VARCHAR,DATEADD(DAY,CONVERT(INT,CONCAT(ASCII(SUBSTRING(B1_USERLGI,12,1)) - 50, ASCII(SUBSTRING(B1_USERLGI,16,1)) - 50) + IIF(SUBSTRING(B1_USERLGI,08,1) = '<',10000,0)),'1996-01-01'), 103)
ELSE
''
END AS DATA_INCL,
USR_A.USR_ID AS USRALT_COD,
USR_A.USR_NOME AS USRALT_NOM,
CASE WHEN SUBSTRING(B1_USERLGA, 03, 1) != ' 'AND B1_USERLGA != ''THEN
CONVERT(VARCHAR,DATEADD(DAY,CONVERT(INT,CONCAT(ASCII(SUBSTRING(B1_USERLGA,12,1)) - 50, ASCII(SUBSTRING(B1_USERLGA,16,1)) - 50) + IIF(SUBSTRING(B1_USERLGA,08,1) = '<',10000,0)),'1996-01-01'), 103)
ELSE ''
END AS DATA_ALT
FROM
SB1990 SB1
LEFT JOIN SYS_USR USR_I ON (
USR_I.USR_ID = LEFT(
SUBSTRING(B1_USERLGI, 11, 1) +
SUBSTRING(B1_USERLGI, 15, 1) +
SUBSTRING(B1_USERLGI, 19, 1) +
SUBSTRING(B1_USERLGI, 02, 1) +
SUBSTRING(B1_USERLGI, 06, 1) +
SUBSTRING(B1_USERLGI, 10, 1) +
SUBSTRING(B1_USERLGI, 14, 1) +
SUBSTRING(B1_USERLGI, 01, 1) +
SUBSTRING(B1_USERLGI, 18, 1) +
SUBSTRING(B1_USERLGI, 05, 1) +
SUBSTRING(B1_USERLGI, 09, 1) +
SUBSTRING(B1_USERLGI, 13, 1) +
SUBSTRING(B1_USERLGI, 17, 1) +
SUBSTRING(B1_USERLGI, 04, 1) +
SUBSTRING(B1_USERLGI, 08, 1)
, 6)
)
LEFT JOIN SYS_USR USR_A ON (
USR_A.USR_ID = LEFT(
SUBSTRING(B1_USERLGA, 11, 1) +
SUBSTRING(B1_USERLGA, 15, 1) +
SUBSTRING(B1_USERLGA, 19, 1) +
SUBSTRING(B1_USERLGA, 02, 1) +
SUBSTRING(B1_USERLGA, 06, 1) +
SUBSTRING(B1_USERLGA, 10, 1) +
SUBSTRING(B1_USERLGA, 14, 1) +
SUBSTRING(B1_USERLGA, 01, 1) +
SUBSTRING(B1_USERLGA, 18, 1) +
SUBSTRING(B1_USERLGA, 05, 1) +
SUBSTRING(B1_USERLGA, 09, 1) +
SUBSTRING(B1_USERLGA, 13, 1) +
SUBSTRING(B1_USERLGA, 17, 1) +
SUBSTRING(B1_USERLGA, 04, 1) +
SUBSTRING(B1_USERLGA, 08, 1)
, 6)
)
WHERE
B1_FILIAL = ' '
AND B1_MSBLQL != '1'
AND SB1.D_E_L_E_T_ = ' '
/* Query original:
SELECT
B1_COD,
B1_DESC,
B1_TIPO,
USR_I.USR_ID AS USRINC_COD,
USR_I.USR_NOME AS USRINC_NOM,
CONVERT(VARCHAR,DATEADD(DAY,CONVERT(INT,CONCAT(ASCII(SUBSTRING(B1_USERLGI,12,1)) - 50, ASCII(SUBSTRING(B1_USERLGI,16,1)) - 50)),'1996-01-01'), 112) AS DATA_INC,
USR_A.USR_ID AS USRALT_COD,
USR_A.USR_NOME AS USRALT_NOM,
CASE WHEN SUBSTRING(B1_USERLGA, 03, 1) != ' ' THEN
CONVERT(VARCHAR,DATEADD(DAY,((ASCII(SUBSTRING(B1_USERLGA,12,1)) - 50) * 100 + (ASCII(SUBSTRING(B1_USERLGA,16,1)) - 50)),'19960101'),112)
ELSE ''
END AS DATA_ALT
FROM
SB1990 SB1
LEFT JOIN SYS_USR USR_I ON (
USR_I.USR_ID = (
SUBSTRING(B1_USERLGI, 11, 1) +
SUBSTRING(B1_USERLGI, 15, 1) +
SUBSTRING(B1_USERLGI, 19, 1) +
SUBSTRING(B1_USERLGI, 02, 1) +
SUBSTRING(B1_USERLGI, 06, 1) +
SUBSTRING(B1_USERLGI, 10, 1) +
SUBSTRING(B1_USERLGI, 14, 1) +
SUBSTRING(B1_USERLGI, 01, 1) +
SUBSTRING(B1_USERLGI, 18, 1) +
SUBSTRING(B1_USERLGI, 05, 1) +
SUBSTRING(B1_USERLGI, 09, 1) +
SUBSTRING(B1_USERLGI, 13, 1) +
SUBSTRING(B1_USERLGI, 17, 1) +
SUBSTRING(B1_USERLGI, 04, 1) +
SUBSTRING(B1_USERLGI, 08, 1)
)
)
LEFT JOIN SYS_USR USR_A ON (
USR_A.USR_ID = (
SUBSTRING(B1_USERLGA, 11, 1) +
SUBSTRING(B1_USERLGA, 15, 1) +
SUBSTRING(B1_USERLGA, 19, 1) +
SUBSTRING(B1_USERLGA, 02, 1) +
SUBSTRING(B1_USERLGA, 06, 1) +
SUBSTRING(B1_USERLGA, 10, 1) +
SUBSTRING(B1_USERLGA, 14, 1) +
SUBSTRING(B1_USERLGA, 01, 1) +
SUBSTRING(B1_USERLGA, 18, 1) +
SUBSTRING(B1_USERLGA, 05, 1) +
SUBSTRING(B1_USERLGA, 09, 1) +
SUBSTRING(B1_USERLGA, 13, 1) +
SUBSTRING(B1_USERLGA, 17, 1) +
SUBSTRING(B1_USERLGA, 04, 1) +
SUBSTRING(B1_USERLGA, 08, 1)
)
)
WHERE
B1_FILIAL = ''
AND B1_MSBLQL != '1'
AND SB1.D_E_L_E_T_ = ' '
*/
https://terminaldeinformacao.com/2019/09/12/como-ver-o-conteudo-dos-campos-lgi-e-lga-no-sql-server/
Crédito Total ao Terminal de Informação.