Converting Sybase To SQL Server Issue

[amccoy33]

I need help in converting this Sybase script to run in SQL Server. Some of the main issues are;
DATEFORMAT(WRK_PROCESS_DATE,'YYYYMMDD') AS 'PROC_DATE',
DATEFORMAT(WRK_COMPLETION_DATE_TIME,'YYYYMMDD') AS 'COMPL_DATE',
(CHAR_LENGTH(WRK_COMPLETION_CODES)*5/3)-1)) AS 'CODE_STR',

Can anyone assist me on converting this into SQL server.

Thanks Mac



SELECT CONVERT(CHAR(4),TCR_CONTRACTOR_CODE) AS 'CTR', CONVERT(CHAR(9),UPPER(CNT_REGION_NAME)) + ' ' AS 'SYSTEMA',
CONVERT(CHAR(4),WRK_TECH_NUMBER) AS 'TECH', DATEFORMAT(WRK_COMPLETION_DATE_TIME,'YYYYMMDD') AS 'COMPL_DATE',
CONVERT(CHAR(17),WRK_WO_NUMBER) AS 'WO_NUM', DATEFORMAT(WRK_PROCESS_DATE,'YYYYMMDD') AS 'PROC_DATE',
CONVERT(CHAR(30),SUBSTRING(SUBSTRING(WRK_COMPLETION_CODES,1,2) + '0' + SUBSTRING(WRK_COMPLETION_CODES,3,1) + '-' +
SUBSTRING(WRK_COMPLETION_CODES,4,2) + '0' + SUBSTRING(WRK_COMPLETION_CODES,6,1) + '-' +
SUBSTRING(WRK_COMPLETION_CODES,7,2) + '0' + SUBSTRING(WRK_COMPLETION_CODES,9,1) + '-' +
SUBSTRING(WRK_COMPLETION_CODES,10,2) + '0' + SUBSTRING(WRK_COMPLETION_CODES,12,1) + '-' +
SUBSTRING(WRK_COMPLETION_CODES,13,2) + '0' + SUBSTRING(WRK_COMPLETION_CODES,15,1) + '-' +
SUBSTRING(WRK_COMPLETION_CODES,16,2) + '0' +
SUBSTRING(WRK_COMPLETION_CODES,18,1),1,(CHAR_LENGTH(WRK_COMPLETION_CODES)*5/3)-1)) AS 'CODE_STR',
CONVERT(CHAR(2),WRK_WO_TYPE) AS 'WO_TYPE', CONVERT(CHAR(1),WRK_STATUS_CODE) AS 'WO_STAT',
CONVERT(CHAR(20),WRK_SUBSCRIBER_NAME) AS 'NAME',
CONVERT(CHAR(26),HSE_LINE_1_ADDRESS) AS 'SVC_ADDR1',
CONVERT(CHAR(3),WRK_CHANGED_OPR_ID) AS 'OPR'
FROM WORKORDER, HOUSE, COUNTY, TECH_RANGE
WHERE WRK_HOUSE_NUMBER = HSE_HOUSE_NUMBER
AND WRK_SYS_PRIN_ID = CNT_SYS_PRIN_ID
AND WRK_AGENT_ID = CNT_AGENT_ID
AND WRK_SYS_PRIN_ID = TCR_SYS_PRIN_ID
AND WRK_TECH_NUMBER BETWEEN TCR_TECH_LOW AND TCR_TECH_HIGH
AND WRK_SYS_PRIN_ID IN ('82231300','82231400','82231500','82231700','82231800')
AND WRK_WO_TYPE NOT IN ('TC','RT','NP','CR','FC','QA')
AND WRK_STATUS_CODE IN ('C','D')
AND WRK_PROCESS_DATE >= @AD_BEGIN_DATE
AND WRK_PROCESS_DATE < DATEADD(DD,1,@AD_END_DATE)
AND TCR_CONTRACTOR_CODE <> '????'
AND ISNULL(@AS_CONTRACTOR,TCR_CONTRACTOR_CODE) = TCR_CONTRACTOR_CODE
AND SUBSTRING(SUBSTRING(WRK_COMPLETION_CODES,1,2) + '0' + SUBSTRING(WRK_COMPLETION_CODES,3,1) + '-' +
SUBSTRING(WRK_COMPLETION_CODES,4,2) + '0' + SUBSTRING(WRK_COMPLETION_CODES,6,1) + '-' +
SUBSTRING(WRK_COMPLETION_CODES,7,2) + '0' + SUBSTRING(WRK_COMPLETION_CODES,9,1) + '-' +
SUBSTRING(WRK_COMPLETION_CODES,10,2) + '0' + SUBSTRING(WRK_COMPLETION_CODES,12,1) + '-' +
SUBSTRING(WRK_COMPLETION_CODES,13,2) + '0' + SUBSTRING(WRK_COMPLETION_CODES,15,1) + '-' +
SUBSTRING(WRK_COMPLETION_CODES,16,2) + '0' +
SUBSTRING(WRK_COMPLETION_CODES,18,1),1,(CHAR_LENGTH(WRK_COMPLETION_CODES)*5/3)-1) <> ' '
ORDER BY CTR, SYSTEMA, TECH, COMPL_DATE, WO_NUM

 

[bborissov]

Not sure if that what you want (I never worked with sybase):

 DATEFORMAT(WRK_PROCESS_DATE,'YYYYMMDD') AS 'PROC_DATE',
 DATEFORMAT(WRK_COMPLETION_DATE_TIME,'YYYYMMDD') AS 'COMPL_DATE',
(CHAR_LENGTH(WRK_COMPLETION_CODES)*5/3)-1)) AS 'CODE_STR',


-- Must be
replace(convert(varchar(10), WRK_PROCESS_DATE,102),'.','') AS Proc_Date,
replace(convert(varchar(10), WRK_COMPLETION_DATE_TIME,102),'.','') AS COMPL_DATE,
(LEN(WRK_COMPLETION_CODES)*5/3)-1)) AS CODE_STR

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.