All geek questions in one place

Data set in datetime in sql with group

Aggregate Functions Available for SQL

AVG() - Returns the average value COUNT() - Returns the number of rows FIRST() - Returns the first value LAST() - Returns the last value MAX() - Returns the largest value MIN() - Returns the smallest value SUM() - Returns the sum

Do I need to apply an aggregate function in a datetime field? Not indicated there. Max (), Min () will not work. I will need either

  • return last date
  • return early date

Is it possible. Can I somehow implement it?

+4
source share
3 answers

min () and max () work fine with dates

you can also do

the last

 select top 1 * from Table order by SomeDate desc

early

 select top 1 * from Table order by SomeDate

BTW SQL Server does not have first () and last () functions

+8
source

Of course, MIN and MAX work.

 Select Min (MyDate) MinDate, MAX (Mydate) MaxDate, COUNT (Mydate) NumDates FROM ( Select GETDATE() + 5 MyDate UNION Select GETDATE()+4 UNION Select GETDATE()+3 UNION Select GETDATE()+2 UNION Select GETDATE()+1 UNION Select GETDATE()-0 UNION Select GETDATE()-1 UNION Select GETDATE()-2 UNION Select GETDATE()-3 UNION Select GETDATE()-4 UNION Select GETDATE()-5 ) DateList

will return

 MinDate MaxDate NumDates ----------------------- ----------------------- ----------- 2011-11-27 13:14:47.013 2011-12-07 13:14:47.013 11 (1 row(s) affected)

However, not all units work. SUM , AVG do not work, and you will receive a message:

 Msg 8117, Level 16, State 1, Line 4 Operand data type datetime is invalid for avg operator.
+2
source

Yes, we can, if the date is present as a table column. Like we have one tbldate table that contains a column named Datecolmn . then use the sql query to get the maximum value of this column. how

 Select Max(Datecolmn ) from tbldate

and it gives you the max date that is present in this table .....

0
source

Source: https://habr.com/ru/post/1384379/

More articles:


All Articles