Click here to Skip to main content
14,177,166 members
Rate this:
 
Please Sign up or sign in to vote.
Dear All, 

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"  
  
   
MAzeem


What I have tried:

not sure how to convert that to sql one
Posted
Updated 24-Feb-19 23:47pm
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

First understand what VC_Days, VC_Hrs, VC_Min, VC_Secons are and get rid of those ridiculous calculations.

It appears that you are trying to express some dates as days, hours, minutes and seconds by truncating a value (note that 86400 = 60 * 60 * 24 - i.e. the number of seconds in a day)

Without seeing some sample data and the actual results I'm not going to go into great depth but the SQL function you probably need is DATEPART (Transact-SQL) - SQL Server | Microsoft Docs[^]
At a push you might want to look at DATEDIFF (Transact-SQL) - SQL Server | Microsoft Docs[^]

To prove it first you could convert the Oracle query and compare results - there is also DatePart[^] and DATEDIFF[^] in Oracle
   
Comments
Maciej Los 25-Feb-19 6:48am
   
5ed!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Start by creating a FUNCTION for all those duplicate calculations.

Note the constants and variables (parameters).
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01 | 2.8.190526.1 | Last Updated 25 Feb 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100