CODEWITHSUNDEEP
sqlsql-servergroup-by
Jeet Bhatt
Jeet Bhatt

Reputation: 778

Apply date value in group by but SELECT statement output field getting DateTime value

Table Structure:

ID   DeviceNumber   CreatedDate
1      101          2018-07-07 07:50:19.0000000
2      101          2018-07-07 01:50:19.0000000
3      101          2018-07-07 06:50:19.0000000
4      101          2018-07-08 02:50:19.0000000

Output:

ID   DeviceNumber   CreatedDate
1      101          2018-07-07 07:50:19.0000000
2      101          2018-07-08 02:50:19.0000000

Here i'm applying GROUPBY clause in CreatedDate field thats why i seperated Day-Month-Year, but in query output i need Date and Time in CreatedDate field value.

My SQL Statement:

SELECT DeviceNumber,  
Convert(varchar(30),Concat(CalMonth, '-' , CalDay, '-' ,CalYear),102)  as CreateDateTime
FROM 
(
select DeviceNumber, DAY(CreatedDate) as CalDay, MONTH(CreatedDate) as CalMonth, YEAR(CreatedDate) as CalYear 
from TableDevice
) AS T
GROUP BY DeviceNumber, CalDay, CalMonth, CalYear

In output i need DeviceNumber and CreatedDate (with Date and Time)

Thanks in an advancd

Upvotes: 0

Views: 85

Answers (3)

PPik
PPik

Reputation: 11

As previous responses stated you need to specify what time do you want to associate with aggregated date?

But if you wanted to have date and time in separate fields use DATE(CreatedDate) and TIME(CreatedDate)

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

you need max() with group by date

select DeviceNumber, max(CreatedDate) as CreatedDate
from TableDevice
group by DeviceNumber, convert(date,CreatedDate)

If needs all fields in selection then you can use corelated subquery

select t1.* from TableDevice t1
where CreatedDate= (select max(CreatedDate)
                    from TableDevice t2
                    where   convert(date,t2.CreatedDate)=convert(date,t1.CreatedDate)
)

Upvotes: 0

George Menoutis
George Menoutis

Reputation: 7240

It seems that you used the GROUP BY in order to merge all the rows with the same day in one, but you still need a time. Which time, though? You need an aggregation function that will return the appropriate one. Since it's called CreatedDate, I bet it's the first one you need (min).

select DeviceNumber, min(CreatedDate)
from TableDevice
group by DeviceNumber, convert(date,CreatedDate)

The convert(date,CreatedDate) is an easier way to group by excluding the time part: The conversion will only keep the date and not the time.

Upvotes: 1

Related Questions