Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Convert Oracle SQL Syntax to SQL Server Syntax

Options
  • 10-07-2007 10:21am
    #1
    samelterrance
    Closed Accounts Posts: 120 ✭✭


    Hi,

    I was hoping someone may be able to help or perhaps point me in the right direction. I have done some research but I cannot find any conversion tools on google.

    Basically, I have this query in Oracle and would like it to run on SQL but certain functions are not available.

    select thr.emp_employee_no thr_emp_no ,
    thr.thr_start_date thr_start_date,
    thr.thr_end_date thr_end_date,
    thr.thr_status thr_status,
    thr.thr_created_by thr_created_by,
    thr.thr_created_date thr_created_date,
    thr.thr_cabs_batch_member_yn thr_batch_yn,
    thr.batch_id thr_batch_id,
    to_char((to_date(thr.thr_start_date,'dd-MON-yy')), 'fmDay') as Day01,
    to_char((to_date(thr.thr_start_date+1,'dd-MON-yy')), 'fmDay') as Day02,
    to_char((to_date(thr.thr_start_date+2,'dd-MON-yy')), 'fmDay') as Day03,
    to_char((to_date(thr.thr_start_date+3,'dd-MON-yy')), 'fmDay') as Day04,
    to_char((to_date(thr.thr_start_date+4,'dd-MON-yy')), 'fmDay') as Day05,
    to_char((to_date(thr.thr_start_date+5,'dd-MON-yy')), 'fmDay') as Day06,
    to_char((to_date(thr.thr_start_date+6,'dd-MON-yy')), 'fmDay') as Day07,
    to_char((to_date(thr.thr_start_date+7,'dd-MON-yy')), 'fmDay') as Day08,
    to_char((to_date(thr.thr_start_date+8,'dd-MON-yy')), 'fmDay') as Day09,
    to_char((to_date(thr.thr_start_date+9,'dd-MON-yy')), 'fmDay') as Day10,
    to_char((to_date(thr.thr_start_date+10,'dd-MON-yy')), 'fmDay') as Day11,
    to_char((to_date(thr.thr_start_date+11,'dd-MON-yy')), 'fmDay') as Day12,
    to_char((to_date(thr.thr_start_date+12,'dd-MON-yy')), 'fmDay') as Day13,
    to_char((to_date(thr.thr_start_date+13,'dd-MON-yy')), 'fmDay') as Day14,
    to_char((to_date(thr.thr_start_date+14,'dd-MON-yy')), 'fmDay') as Day15,
    to_char((to_date(thr.thr_start_date+15,'dd-MON-yy')), 'fmDay') as Day16,
    to_char((to_date(thr.thr_start_date,'dd-MON-yy')), 'dd') as Date01,
    to_char((to_date(thr.thr_start_date+1,'dd-MON-yy')), 'dd') as Date02,
    to_char((to_date(thr.thr_start_date+2,'dd-MON-yy')), 'dd') as Date03,
    to_char((to_date(thr.thr_start_date+3,'dd-MON-yy')), 'dd') as Date04,
    to_char((to_date(thr.thr_start_date+4,'dd-MON-yy')), 'dd') as Date05,
    to_char((to_date(thr.thr_start_date+5,'dd-MON-yy')), 'dd') as Date06,
    to_char((to_date(thr.thr_start_date+6,'dd-MON-yy')), 'dd') as Date07,
    to_char((to_date(thr.thr_start_date+7,'dd-MON-yy')), 'dd') as Date08,
    to_char((to_date(thr.thr_start_date+8,'dd-MON-yy')), 'dd') as Date09,
    to_char((to_date(thr.thr_start_date+9,'dd-MON-yy')), 'dd') as Date10,
    to_char((to_date(thr.thr_start_date+10,'dd-MON-yy')), 'dd') as Date11,
    to_char((to_date(thr.thr_start_date+11,'dd-MON-yy')), 'dd') as Date12,
    to_char((to_date(thr.thr_start_date+12,'dd-MON-yy')), 'dd') as Date13,
    to_char((to_date(thr.thr_start_date+13,'dd-MON-yy')), 'dd') as Date14,
    to_char((to_date(thr.thr_start_date+14,'dd-MON-yy')), 'dd') as Date15,
    to_char((to_date(thr.thr_start_date+15,'dd-MON-yy')), 'dd') as Date16,
    ts.emp_employee_no ts_emp_no,
    ts.thr_start_date ts_start_date,
    ts.thr_end_date ts_end_date,
    ts.cln_client_no ts_client_no,
    ts.eng_seq ts_eng_seq,
    decode(ts.ttn_ed_eng_narrative, null, cln.cln_short_name||' '||eng.eng_name, cln.cln_short_name||' '||ts.ttn_ed_eng_narrative ) ts_eng_nar,
    ts.ttn_day_1 ts_ttn_day_1,
    ts.ttn_day_2 ts_ttn_day_2,
    ts.ttn_day_3 ts_ttn_day_3,
    ts.ttn_day_4 ts_ttn_day_4,
    ts.ttn_day_5 ts_ttn_day_5,
    ts.ttn_day_6 ts_ttn_day_6,
    ts.ttn_day_7 ts_ttn_day_7,
    ts.ttn_day_8 ts_ttn_day_8,
    ts.ttn_day_9 ts_ttn_day_9,
    ts.ttn_day_10 ts_ttn_day_10,
    ts.ttn_day_11 ts_ttn_day_11,
    ts.ttn_day_12 ts_ttn_day_12,
    ts.ttn_day_13 ts_ttn_day_13,
    ts.ttn_day_14 ts_ttn_day_14,
    ts.ttn_day_15 ts_ttn_day_15,
    ts.ttn_day_16 ts_ttn_day_16,
    ts.ttn_display_order ts_display_order,
    ts.ttn_total_units ts_total_units,
    ts.cln_client_no||ts.eng_seq ts_eng_no,
    cln.cln_short_name||' '||eng.eng_name ts_eng_name,
    emp.emp_short_name emp_short_name,
    emp.emp_inits emp_inits,
    substr(emp.sl_los,1,3) office_code
    from timesheet_header thr,
    timesheet_trans ts,
    engagements eng,
    clients cln,
    employees emp
    where thr.emp_employee_no = ts.emp_employee_no
    and thr.emp_employee_no = emp.emp_employee_no
    and thr.thr_start_date = ts.thr_start_date
    and thr.thr_end_date = ts.thr_end_date
    and ts.cln_client_no = eng.cln_client_no
    and ts.eng_seq = eng.eng_seq
    and eng.cln_client_no = cln.cln_client_no
    and ts.cln_client_no||ts.eng_seq != '999999000'
    and (ts.ttn_day_1 is not null or
    ts.ttn_day_2 is not null or
    ts.ttn_day_3 is not null or
    ts.ttn_day_4 is not null or
    ts.ttn_day_5 is not null or
    ts.ttn_day_6 is not null or
    ts.ttn_day_7 is not null or
    ts.ttn_day_8 is not null or
    ts.ttn_day_9 is not null or
    ts.ttn_day_10 is not null or
    ts.ttn_day_11 is not null or
    ts.ttn_day_12 is not null or
    ts.ttn_day_13 is not null or
    ts.ttn_day_14 is not null or
    ts.ttn_day_15 is not null or
    ts.ttn_day_16 is not null or
    ts.ttn_total_units is not null)
    &p_where
    order by ts.ttn_display_order


    Many Thanks,


Comments

  • Options
    #2
    Evil Phil
    Registered Users Posts: 7,468 ✭✭✭Evil Phil


    That's a whole lotta SQL. It might be easier if you just told us what functions aren't working.


  • Options
    #3
    samelterrance
    Closed Accounts Posts: 120 ✭✭samelterrance


    Hi Phil,

    Sorry about that.

    to_char((to_date(thr.thr_start_date,'dd-MON-yy')), 'fmDay') as Day01

    that's what I'm having difficulties with.

    Thanks,


  • Options
    #4
    Tom Dunne
    Registered Users Posts: 23,212 ✭✭✭✭Tom Dunne


    samelterrance wrote:
    Hi Phil,

    Sorry about that.

    to_char((to_date(thr.thr_start_date,'dd-MON-yy')), 'fmDay') as Day01

    that's what I'm having difficulties with.

    Thanks,

    It's actually not as complicated as it looks.

    Starting from the inside, the to_date function is doing just that - taking thr.thr_start_date and converting it to a date in the format dd-MON-YY. These are (hopfully obvious) day in two letter format, such as Mo, Tu, We etc, month in three letter format, e.g. Mar, Apr, May, etc. and two digit year, 06,07,08 etc.

    The resultant formatted date is then re-formatted using to_char to the 'fmDay' format, which, if memory serves me correctly, is something like "Monday 3rd".

    The final piece, as Day01 is just calling it a name so it can be referred to.


  • Options
    #5
    samelterrance
    Closed Accounts Posts: 120 ✭✭samelterrance


    Hi Tom,

    Thanks for that.
    I know what it's doing but just can't syntax it that way in SQL Server :-(
    as the functions don't exist.

    Cheers
    tom dunne wrote:
    It's actually not as complicated as it looks.

    Starting from the inside, the to_date function is doing just that - taking thr.thr_start_date and converting it to a date in the format dd-MON-YY. These are (hopfully obvious) day in two letter format, such as Mo, Tu, We etc, month in three letter format, e.g. Mar, Apr, May, etc. and two digit year, 06,07,08 etc.

    The resultant formatted date is then re-formatted using to_char to the 'fmDay' format, which, if memory serves me correctly, is something like "Monday 3rd".

    The final piece, as Day01 is just calling it a name so it can be referred to.


  • Options
    #6
    Tom Dunne
    Registered Users Posts: 23,212 ✭✭✭✭Tom Dunne


    Sorry, I thought by explaining it, you might be able to find a corresponding function in SQL server.

    According to Google, the CONVERT() function is what you are looking for. I don't do SQL Server, so I can't give you any more than that.


  • Advertisement
  • Options
    #7
    samelterrance
    Closed Accounts Posts: 120 ✭✭samelterrance


    Hi Tom,

    Had a look at that all right but couldnt' get it working.
    I'll continue googling but thanks for the help, appreciate it.


  • Options
    #8
    koloughlin
    Closed Accounts Posts: 97 ✭✭koloughlin


    I think you're basically converting 10-JUL-07 to Tuesday

    Something like this will work I think, not tested so you may have to play a little with it
    case datepart(dw, convert(datetime, thr.thr_start_date)) 
when 1 then 'Sunday' 
when 2 then 'Monday' 
when 3 then 'Tuesday' 
when 4 then 'Wednesday' 
when 5 then 'Thursday' 
when 6 then 'Friday' 
when 7 then 'Saturday' 
end

    I seem to remember that SQL Server has a function to get the full text of the day from the day number, but I can't remember what it is. So you may not need the case statement


  • Options
    #9
    samelterrance
    Closed Accounts Posts: 120 ✭✭samelterrance


    Hi K,

    Thank you.
    Have it working now.

    select mydate,DATENAME(dw, mydate), right(left(mydate,7),3)
    from testtbl

    2006-03-16 00:00:00.000 Thursday 16
    2006-03-15 00:00:00.000 Wednesday 15
    2007-07-10 00:00:00.000 Tuesday 10

    Thanks for the help all.


  • Options
    #10
    koloughlin
    Closed Accounts Posts: 97 ✭✭koloughlin


    Glad you got it working. You could consider using datepart(dd, mydate) rather than the right and left functions. Either will work though :)


  • Options
    #11
    samelterrance
    Closed Accounts Posts: 120 ✭✭samelterrance


    You're a gentleman, even better now.

    Thanks very much.
    koloughlin wrote:
    Glad you got it working. You could consider using datepart(dd, mydate) rather than the right and left functions. Either will work though :)


  • Advertisement
Advertisement