How to Select Max Date and Max Time from the Max Date?

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

 

Addend Analytics is a Microsoft Gold Partner based in Mumbai, India, and a branch office in the U.S.

Addend has successfully implemented 100+ Microsoft Power BI and Business Central projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia.

Get a free consultation now by emailing us or contacting us.