Oracle Query to SQL query conversion issuesRSS

• • Reply
  mmazeemahmad

  Member

  101 Points

  392 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 ..

  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"  
    
     

• • Reply
  Ackerly Xu

  Contributor

  2620 Points

  1047 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.

   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 "Mark as Answer" the responses that resolved your issue.