datetime column

Question

Hi

How can i get the max datetime on my column for time of entry.

Answer 1

Try this query:

select max(TimeOfEntry) as [max datetime] from MyTable

Answer 2

Hi

It doesn't work as it still returns the same values as the timeofentry

Answer 3

I expect the max datetime for each of the dates as the timeofentry

Then aggregate on the date portion, like

select cast(TimeOfEntry as date) as DateOfEntry, max(TimeOfEntry) as MaxTime
from yourTable
group by cast(TimeOfEntry as date)

Answer 4

Hi @Phillip Noah ,

Welcome to the microsoft TSQL Q&A forum!

Please also check:

SELECT MAX(TimeOfEntry) OVER(PARTITION BY CAST(TimeOfEntry AS DATE)) AS maxdatetime  
FROM yourTable  

If you have any question, please feel free to let me know.

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

This is the Query i run below

SELECT [StatusEntryID]
,cast(TimeOfEntry as date) as DateOfEntry, max(TimeOfEntry) as MaxTime
,[VehicleID]
,[Latitude]
,[Longitude]
,[NSIndicator]
,[EWIndicator]
,[SpeedKM]
,[Course]
,[Reserved]
,[StatusLogCalculated]
FROM [Simecs].[dbo].[VehicleStatus]
group by [StatusEntryID], cast(TimeOfEntry as date),[VehicleID]
,[Latitude]
,[Longitude]
,[NSIndicator]
,[EWIndicator]
,[SpeedKM]
,[Course]
,[Reserved]
,[StatusLogCalculated]

That was not the query you were given. That query will give you the max query of every combination of Lat, Long, NSIndicator etc.

desired output

>

Dateofentry maxtime VehicleID
2016-02-03 2016-02-03 11:15:44 E1201
2016-02-04 2016-02-03 11:25:18 E1201

If you only want three columns, don't include other columns. Try:

SELECT cast(TimeOfEntry as date) as DateOfEntry, max(TimeOfEntry) as MaxTime
,[VehicleID]
FROM [
group by cast(TimeOfEntry as date),[VehicleID]

Or did you mean to say that you want the last row for the day for all vehicles? (I'm guessing wildly, as you are not doing a very good job of explaining what you want.) If so, try this:

; WITH CTE AS (
    SELECT [StatusEntryID]
      ,cast(TimeOfEntry as date) as DateOfEntry, TimeOfEntry
       ,[VehicleID]
       ,[Latitude]
       ,[Longitude]
       ,[NSIndicator]
       ,[EWIndicator]
       ,[SpeedKM]
       ,[Course]
       ,[Reserved]
       ,[StatusLogCalculated]
       , row_number() OVER (PARTITION BY VehicleID, cast(TimeOfEntry as date)
                            ORDER BY TimeEntry DESC) AS rowno
    FROM [Simecs].[dbo].[VehicleStatus]
)
SELECT [StatusEntryID], cast(TimeOfEntry as date),[VehicleID],
       TimeOfEntry
       ,[Latitude]
       ,[Longitude]
       ,[NSIndicator]
       ,[EWIndicator]
       ,[SpeedKM]
       ,[Course]
       ,[Reserved]
       ,[StatusLogCalculated]
FROM   CTE
WHERE rowno = 1