Converting DATEFORMAT and HOURS from Sybase to SQL 2000

[amccoy33]

I have a script in which I need to convert into SQL 2000 syntax and need help with the Dateformat YYMMDD, HHMM, and Hours....

SELECT WRK_WO_NUMBER AS WO_Number,

DATEFORMAT(WRK_ENTERED_DATE_TIME,'yyyy/mm/dd')AS Date_Entered,

DATEFORMAT(WRK_ENTERED_DATE_TIME,'hhmm') AS Time_Entered,

DATEFORMAT(WRK_COMPLETION_DATE_TIME,'yyyy/mm/dd') AS Date_Completed,

DATEFORMAT(WRK_COMPLETION_DATE_TIME,'hhmm') AS Time_Completed,
WRK_WO_REASONS AS WO_Reasons,
WRK_CUSTOMER_REQ_FLAG AS Cust_Req,

HOURS(WRK_ENTERED_DATE_TIME,WRK_COMPLETION_DATE_TIME) AS HOURS_COMPL

FROM WORKORDER

WHERE WRK_COMPLETION_DATE_TIME > '2006-01-22'
AND WRK_COMPLETION_DATE_TIME < '2006-02-21'
AND WRK_STATUS_CODE = 'C'
AND WRK_INSTALL_TIME_CODE <> 'N'
AND WRK_SYS_PRIN_ID IN ('82231700','82231800')
AND HOURS(WRK_ENTERED_DATE_TIME,WRK_COMPLETION_DATE_TIME) > 24

 

[r937]

can you use yyyy-mm-dd instead of yyyy/mm/dd?

if so, use CONVERT(varchar(10),datecolumn,120)

otherwise, you need to feed the CONVERT into a REPLACE to change the slashes

can you use hh:mm instead of hhmm?

if so, use CONVERT(varchar(5),datecolumn,108)

otherwise, you need to feed the CONVERT into a REPLACE to remove the colon

please note: in future, please post your sql server questions in the sql server forum, thanks



r937.com | rudy.ca