ORACLE to SQL SERVER Code conversion problem

• jbrewer

  Default port

  Points: 1453

  More actions

  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;

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  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.

• jbrewer

  Default port

  Points: 1453

  More actions

  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?

• jbrewer

  Default port

  Points: 1453

  More actions

  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;