In this Blog, I will show you How to select Max Date and Max Time for the Date.
I have this kind of table:
CREATE TABLE Dateandtime1(
[Date_updated] [date],
[Time_updated] [Time] )
We can create two columns that have both date and time within them “DATE and TIME”. This column holds both these values in the format YYYY:MM: DD HH:MM: SS. This can be very handy in many situations.
There I have currently this kind of data:
You can see that all the years are the same, but the date and time are different In this DEMO Table.
If you wanted to return the maximum date and maximum time from a Demo table you may do something like:
select max(date_updated), max(Time_updated) from Dateandtime1 — Wrong Way
The above Screenshot shows the wrong way to find “Max Date and Max Time from the MaxDate”, this will actually do is return the maximum date and the maximum time for any date.
I wanted Max Date and “Max Time From the Max Date”
SELECT Date_updated,Time_updated
FROM Dateandtime1 WHERE Time_updated
IN(SELECT MAX(Time_updated) FROM Dateandtime1 WHERE Date_updated IN(SELECT MAX(Date_updated) FROM Dateandtime1)) – – Right Way
A quick explanation for the Above Query:-If you skip to the last line of the above Query you will see the simple logic behind what is happening here. In the last set of brackets “MAX(Date_updated) FROM Dateandtime1”, you are selecting the MAX date and because of this condition, you are now selecting the max time of this date. You can look at this as a small subquery first which is used by the time query.
We are getting the maximum date and the maximum time from this date.
Rahul Prajapati
Data Analyst
Addend Analytics