locked
Oracle Query to SQL query conversion issues RRS feed

  • Question

  • User670644709 posted

    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"  
      
       

    Saturday, February 23, 2019 2:58 PM

All replies

  • User-893317190 posted

    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

    Monday, February 25, 2019 2:48 AM