Post reply

ORACLE to SQL SERVER Code conversion problem

  • October 8, 2011 at 7:29 am

    #260131

    We are converting ORACLE to SQL Server and I need help with to_date, to_char and sysdate in the 2 oracle scripts below. I appreciate any help that I can get converting this code.

    ORACLE script #1

    select zone_name from ch_zonemap

    where company = ':company' and

    deptno = ':deptno' and

    (to_date('19000101:feed_time', 'yyyymmddHH:MI AM') between

    to_date('19000101'||to_char(begtime_1,'HH24:MI'), 'yyyymmddHH24:MI') and

    to_date('19000101'||to_char(endtime_1,'HH24:MI'), 'yyyymmddHH24:MI') )

    OR

    (to_date('19000101:feed_time', 'yyyymmddHH:MI AM') between

    to_date('19000101'||to_char(ch_zonemap.begtime_2,'HH24:MI'), 'yyyymmddHH24:MI') and

    to_date('19000101'||to_char(ch_zonemap.endtime_2,'HH24:MI'), 'yyyymmddHH24:MI'))

    ORACLE script #2

    SELECT timesheetitem.employeeid,

    count(*) as "EARLY"

    FROM timesheetitem, vp_wfcexceptionv42

    WHERE (vp_wfcexceptionv42.timesheetitemid = timesheetitem.timesheetitemid)

    AND (vp_wfcexceptionv42.exceptiontype = 'EARLY')

    AND ((to_char(timesheetitem.startdtm, 'YYYY-MM-DD HH24:MI:SS') > to_char(sysdate -7, 'YYYY-MM-DD "06:59:00"' ))

    AND (to_char(timesheetitem.startdtm, 'YYYY-MM-DD HH24:MI:SS') < to_char(sysdate, 'YYYY-MM-DD "07:00:00"' )))

    AND (vp_wfcexceptionv42.inpunchsw ='1')

    GROUP BY timesheetitem.employeeid

    ORDER BY timesheetitem.employeeid;

  • October 8, 2011 at 4:11 pm

    #1392128

    SYSDATE, research getdate()

    TO_CHAR() and TO_DATE(), research cast()

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • October 8, 2011 at 5:18 pm

    #1392135

    Thank you but I have done that and I still cannot get the code correct. Can anyone help me with the coding?

  • October 8, 2011 at 5:47 pm

    #1392137

    I was able to get the second script converted but I still need help with the first script.

    Second Script SQL Server code

    SELECT T.employeeid,

    count(*) as "EARLY"

    FROM timesheetitem T, vp_wfcexceptionv42 VP

    WHERE (VP.timesheetitemid = T.timesheetitemid)

    AND (VP.exceptiontype = 'EARLY')

    AND ((CONVERT(DATETIME, T.STARTDTM, 120) > LEFT(convert(varchar, getdate()-7, 120),10) + ' 06:59:00' )

    AND (CONVERT(DATETIME, T.STARTDTM, 120) < LEFT(convert(varchar, getdate(), 120),10) + ' 07:00:00' ))

    AND (VP.inpunchsw ='1')

    GROUP BY T.employeeid

    ORDER BY T.employeeid;

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply