Modify query sql

I have a query to aggregate (compress) data from 1 min to any other time frame, and it works perfectly.

Use StockDataFromSella;
DECLARE @D1 DateTime 
DECLARE @D2 DateTime 
DECLARE @Interval FLOAT 

SET @D1  = '2008-09-21T09:00:00.000' 
SET @D2  = '2010-10-20T17:30:00.000'
SET @Interval = 15 

;WITH  
L0 AS (SELECT 1 AS c UNION ALL SELECT 1), 
L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),开发者_高级运维 
L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B), 
L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B), 
L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B), 
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS i FROM L4), 
Ranges AS( 
SELECT  
      DATEADD(MINUTE,@Interval*(i-1),@D1) AS StartRange, 
      DATEADD(MINUTE,@Interval*i,@D1) AS NextRange 
FROM Nums where i <= 1+CEILING(DATEDIFF(MINUTE,@D1,@D2)/@Interval)) 
,cte AS ( 
SELECT  
     *  
     ,ROW_NUMBER() OVER (PARTITION BY Simbolo,r.StartRange ORDER BY [DataOra])      AS RN_ASC  
     ,ROW_NUMBER() OVER (PARTITION BY Simbolo,r.StartRange ORDER BY [DataOra] DESC) AS RN_DESC 
FROM Ranges r 
JOIN dbo.tbl1MinENI p  ON p.[DataOra] >= r.StartRange and p.[DataOra] < r.NextRange ) 
SELECT  
      Simbolo, 
      MAX(CASE WHEN RN_ASC=1 THEN [DataOra] END) AS DataOra, 
      MAX(CASE WHEN RN_ASC=1 THEN [Apertura] END) AS [Apertura], 
      MAX(Massimo) Massimo, 
      MIN(Minimo)  Minimo, 
      MAX(CASE WHEN RN_DESC=1 THEN [Chiusura] END) AS [Chiusura],
      SUM(Volume) Volume
      /*MAX(CASE WHEN RN_DESC=1 THEN [DataOra] END) AS ChiusuraDataOra*/
FROM cte 
GROUP BY Simbolo,StartRange 
ORDER BY DataOra

I would like to split second column DataOra(DateTime) into two diferent columns, one for Date (if possibile in dd/mm/yyyy format) and the second for Time only. Any help very appreciated, thanks. Alberto

The easiest way to get these values would be to use CONVERT. SQL Server has some built-in date formatting when you convert a Date.

 CONVERT(VARCHAR, MAX(CASE WHEN RN_ASC=1 THEN [DataOra] END), 103) AS DataOraDate, 
 CONVERT(VARCHAR, MAX(CASE WHEN RN_ASC=1 THEN [DataOra] END), 114) AS DataOraTime, 

The two codes (103 and 114) correspond to the formats that you want. See http://msdn.microsoft.com/en-us/library/ms187928.aspx for more details about those and the other formats you can convert to.

As it seems you just only need to change the select. Instead of this

MAX(CASE WHEN RN_ASC=1 THEN [DataOra] END) AS DataOra, 

Use this:

CONVERT(VARCHAR(8),MAX(CASE WHEN RN_ASC=1 THEN [DataOra] END),108) AS HourMinuteSecond,
CONVERT(VARCHAR(8),MAX(CASE WHEN RN_ASC=1 THEN [DataOra] END),101) AS DateOnly,

Hope it helps.