Converting Sybase To SQL
-
How do you convert Max(Date(Job_Changed_date)) for SQL to read if the data type is a TIMESTAMP in Sybase and a Datetime in SQL 2000??
Thank you,
Mac
SELECT HSE_SYS_PRIN_ID,HSE_HOUSE_NUMBER, A.DISCODTE, MAX(B.JOB_CHANGED_DATE) CONNDATE
FROM HOUSE, JOB B, JOB_SERVICE,
(SELECT DISTINCT JOB_HOUSE_NUMBER,
MAX(DATE(JOB_CHANGED_DATE)) DISCODTE
FROM JOB B
WHERE JOB_STATUS = 'C'
AND JOB_JOB_TYPE in ('VD','NP')
GROUP BY JOB_HOUSE_NUMBER) A
WHERE HSE_HOUSE_NUMBER = B.JOB_HOUSE_NUMBER
AND HSE_HOUSE_NUMBER = A.JOB_HOUSE_NUMBER
AND HSE_SYS_PRIN_ID LIKE '8223%'
AND B.JOB_STATUS = 'C'
AND B.JOB_JOB_TYPE in ('RC','RS')
AND B.JOB_PROCESS_DATE >= '2006-01-22'
AND B.JOB_PROCESS_DATE <= '2006-02-21'
AND B.JOB_JOB_NUMBER = JBS_JOB_NUMBER
AND JBS_DISCOUNT_CODE IN ('FA','FD','FG','FJ','FM',
'WR','WU','WX','W4','W7','1W','E8','D8','J2','J5','J8','J9')
AND B.JOB_PROCESS_DATE > A.DISCODTE
GROUP BY HSE_SYS_PRIN_ID,HSE_HOUSE_NUMBER, A.DISCODTE
-
It's been a while, but as I recall, you don't. Sybase's TIMESTAMP is a poorly named datatype, and actually there is no way to extract the date/time from the value. The value is (usually!) constantly incrementing, so it always unique and is useful for comparing two TIMESTAMP's on the same instance to see which happened first, but that is all. As I recall, the current TIMESTAMP is incremented with each write operation, but does not reflect any particular clock time.
Does Max(Date(Job_Changed_date)) return a date for you in Sybase if Job_Changed_Date is a TIMESTAMP? That wouldn't seem right to me (assuming my admittedly faulty memory is right. Like I said, it's been a while.)
-
Actually, I had to do some research but you need to MAX(Convert(char(10),Job_Changed_date,110)) DISCODTE to get the date.
Thanks for your support anyway....
Mac
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply