nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Child Item

Convert Oracle SQL Syntax to SQL Server Syntax

samelterrance

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,

Find more posts tagged with

Quick Links

Comments

Evil Phil

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

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,

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.

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.

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.

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.

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

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.

koloughlin

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

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