Converting Sybase Syntax to SQL Server Syntax

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Converting Sybase Syntax to SQL Server Syntax

Author

Topic

Mac39
Starting Member

2 Posts

Posted - 2006-04-26 : 17:56:07

I need help on converting this Sybase script to SQL. Any help will be greatly appreciated from the SQL Team.

Thanks Mac39

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 >= '2006-01-22'
AND WRK_PROCESS_DATE < Dateadd(DD,1,'2006-02-21')
AND TCR_CONTRACTOR_CODE <> '????'
AND Isnull('CTIS',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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-26 : 18:02:15

I've never used Sybase before, so I could be wrong...

You can use CONVERT with a style (specifically style 112) instead of the DATE_FORMAT Sybase function. You can use DATALENGTH instead of the Char_length Sybase function.

Check SQL Server Books Online for CONVERT and DATALENGTH to see if they fit your needs.

Tara Kizer
aka tduggan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-26 : 18:02:15

You can use the CONVERT function to format the date.

Not sure what the Char_length function does, but you might try the LEN
or DATALENGTH functions.

You can read about the correct function syntax in SQL Server Books Online.

CODO ERGO SUM

Mac39
Starting Member

2 Posts

Posted - 2006-04-27 : 07:57:06

Thanks for all you guys help,

I converted the Sybase DATEFORMAT;
DATEFORMAT(WRK_COMPLETION_DATE_TIME,'YYYYMMDD')

To SQL Server;
Replace(Convert(VARCHAR(10),JOB_PROCESS_DATE,102),'.','') AS 'PROC_DATE'

I converted the Sybase Char_Length;
(CHAR_LENGTH(WRK_COMPLETION_CODES)*5/3)-1)) AS 'CODE_STR'

To SQL Server;
(Datalength(JOB_COMPLETION_CODES) * 5 / 3) - 1)) AS 'CODE_STR'

This return a balance and complete data set for SYBASE and SQL SERVER

Thanks again team....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources