can someone please help to convert the following oracle query to sql specific please.. as i am really struggling with that ..
SELECT "SAMPLE"."ID_NUMERIC","SAMPLE"."BATCH_NO","SAMPLE"."STATUS","SAMPLE"."RECD_DATE","SAMPLE"."DATE_AUTHORISED","SAMPLE"."TEMPLATE_ID","SAMPLE"."RELEASE_DATE","SAMPLE"."LOGIN_DATE","SAMPLE"."DATE_COMPLETED",
TRUNC((((86400*(DATE_COMPLETED-RECD_DATE))/60)/60)/24) AS VC_Days,
TRUNC(((86400*(DATE_COMPLETED-RECD_DATE))/60)/60)-(24*(TRUNC((((86400*(DATE_COMPLETED-RECD_DATE))/60)/60)/24))) AS VC_Hrs,
TRUNC((86400*(DATE_COMPLETED-RECD_DATE))/60)-(60*(TRUNC(((86400*(DATE_COMPLETED-RECD_DATE))/60)/60))) AS VC_Min,
TRUNC(86400*(DATE_COMPLETED-RECD_DATE))-(60*(TRUNC((86400*(DATE_COMPLETED-RECD_DATE))/60))) AS VC_Sec,
TRUNC((((86400*(RELEASE_DATE-RECD_DATE))/60)/60)/24) AS VR_Days,
TRUNC(((86400*(RELEASE_DATE-RECD_DATE))/60)/60)-(24*(TRUNC((((86400*(RELEASE_DATE-RECD_DATE))/60)/60)/24))) AS VR_Hrs,
TRUNC((86400*(RELEASE_DATE-RECD_DATE))/60)-(60*(TRUNC(((86400*(RELEASE_DATE-RECD_DATE))/60)/60))) AS VR_Min,
TRUNC(86400*(RELEASE_DATE-RECD_DATE))-(60*(TRUNC((86400*(RELEASE_DATE-RECD_DATE))/60))) AS VR_Sec,
TRUNC((((86400*(DATE_AUTHORISED-DATE_COMPLETED))/60)/60)/24) AS CA_Days,
TRUNC(((86400*(DATE_AUTHORISED-DATE_COMPLETED))/60)/60)-(24*(TRUNC((((86400*(DATE_AUTHORISED-DATE_COMPLETED))/60)/60)/24))) AS CA_Hrs,
TRUNC((86400*(DATE_AUTHORISED-DATE_COMPLETED))/60)-(60*(TRUNC(((86400*(DATE_AUTHORISED-DATE_COMPLETED))/60)/60))) AS CA_Min,
TRUNC(86400*(DATE_AUTHORISED-DATE_COMPLETED))-(60*(TRUNC((86400*(DATE_AUTHORISED-DATE_COMPLETED))/60))) AS CA_Sec,
TRUNC((((86400*(DATE_AUTHORISED-RECD_DATE))/60)/60)/24) AS VA_Days,
TRUNC(((86400*(DATE_AUTHORISED-RECD_DATE))/60)/60)-(24*(TRUNC((((86400*(DATE_AUTHORISED-RECD_DATE))/60)/60)/24))) AS VA_Hrs,
TRUNC((86400*(DATE_AUTHORISED-RECD_DATE))/60)-(60*(TRUNC(((86400*(DATE_AUTHORISED-RECD_DATE))/60)/60))) AS VA_Min,
TRUNC(86400*(DATE_AUTHORISED-RECD_DATE))-(60*(TRUNC((86400*(DATE_AUTHORISED-RECD_DATE))/60))) AS VA_Sec
FROM "SAMPLE"
It seems your oracle query only selects date ,hour, seconds between two date.
SqlServer has function for date calculation. Below is my sample.
getDate() gets the current date , dateadd(day,1,getDate()) gets tomorrow's date.
datediffer(interval, date1,date2) gets the difference between date2 and date1 , interval specifies unit of the result
You could specify day to get the day difference , hour to get the hour difference and so on.
select datediff(day,getDate(),dateadd(day,1,getDate()))
select datediff(hour,getDate(),dateadd(day,1,getDate()))
select datediff(minute,getDate(),dateadd(day,1,getDate())) -- get minute difference
select datediff(second,getDate(),dateadd(day,1,getDate())) -- get second difference
So you could write your query similar to the query below in sqlserver
SELECT "SAMPLE"."ID_NUMERIC",
"SAMPLE"."BATCH_NO",
"SAMPLE"."STATUS",
"SAMPLE"."RECD_DATE",
"SAMPLE"."DATE_AUTHORISED",
"SAMPLE"."TEMPLATE_ID",
"SAMPLE"."RELEASE_DATE",
"SAMPLE"."LOGIN_DATE",
"SAMPLE"."DATE_COMPLETED",
datediff(day,RECD_DATE,DATE_COMPLETED) AS VC_Days,
datediff(hour,RECD_DATE,DATE_COMPLETED) AS VC_Hrs,
datediff(minute,RECD_DATE,DATE_COMPLETED) AS VC_Min,
datediff(second,RECD_DATE,DATE_COMPLETED) AS VC_Sec,
datediff(day,RECD_DATE,RELEASE_DATE) AS VR_Days,
datediff(hour,RECD_DATE,RELEASE_DATE) AS VR_Hrs,
datediff(minute,RECD_DATE,RELEASE_DATE) AS VR_Min,
datediff(second,RECD_DATE,RELEASE_DATE) AS VR_Sec,
datediff(day,DATE_COMPLETED,DATE_AUTHORISED) CA_Days,
datediff(hour,DATE_COMPLETED,DATE_AUTHORISED) AS CA_Hrs,
datediff(minute,DATE_COMPLETED,DATE_AUTHORISED) AS CA_Min,
datediff(second,DATE_COMPLETED,DATE_AUTHORISED) AS CA_Sec,
datediff(day,RECD_DATE,DATE_AUTHORISED) AS VA_Days,
datediff(hour,RECD_DATE,DATE_AUTHORISED) AS VA_Hrs,
datediff(minute,RECD_DATE,DATE_AUTHORISED) AS VA_Min,
datediff(second,RECD_DATE,DATE_AUTHORISED) AS VA_Sec
FROM "SAMPLE"
There may be some differences from what you need in sql query, please change the query according to your requirement when you find any mismatch in my query.
Best regards,
Ackerly Xu
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
101 Points
393 Posts
Oracle Query to SQL query conversion issues
Feb 23, 2019 02:58 PM|mmazeemahmad|LINK
Dear All,
can someone please help to convert the following oracle query to sql specific please.. as i am really struggling with that ..
Contributor
3500 Points
1300 Posts
Re: Oracle Query to SQL query conversion issues
Feb 25, 2019 02:48 AM|Ackerly Xu|LINK
Hi mmazeemahmad,
It seems your oracle query only selects date ,hour, seconds between two date.
SqlServer has function for date calculation. Below is my sample.
getDate() gets the current date , dateadd(day,1,getDate()) gets tomorrow's date.
datediffer(interval, date1,date2) gets the difference between date2 and date1 , interval specifies unit of the result
You could specify day to get the day difference , hour to get the hour difference and so on.
So you could write your query similar to the query below in sqlserver
There may be some differences from what you need in sql query, please change the query according to your requirement when you find any mismatch in my query.
Best regards,
Ackerly Xu
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.