How to return last date from a single column of dates?

Question

I have this SQL query, that returns just one single column from a table. This column holds many dates in it, Ex: '05/09/2014', etc.

I would like to also return a second column that has the value of the last computed date it finds from the first (this column will only have one row, or possibly multiple rows all with the same value, either way is fine.

I can't seem to get the LAST() or LAST_VALUE() functions to be able to do this seemingly simple task for me. Any suggestions? Here's my query:

--The converts are just to get the dates in a format I like...
SELECT CONVERT(varchar(10), WeekEnding, 101) WeekEnding,   LAST_VALUE(CONVERT(varchar(10), WeekEnding, 101)) OVER(ORDER BY WeekEnding)
FROM dbo.Weekly

I'm not sure how exactly I should be using the OVER clause.

What version of SQL server are you using?

Matas Vaitkevicius
– Matas Vaitkevicius

2014-05-02 16:13:22 +00:00
Commented May 2, 2014 at 16:13 — Matas Vaitkevicius
– Matas Vaitkevicius, Commented May 2, 2014 at 16:13
SQL Server Management Studio 2012

LaRae White
– LaRae White

2014-05-02 16:21:52 +00:00
Commented May 2, 2014 at 16:21 — LaRae White
– LaRae White, Commented May 2, 2014 at 16:21

Lamak · Accepted Answer · 2014-05-02 16:12:44Z

1

MAX should do:

SELECT  CONVERT(varchar(10), WeekEnding, 101) WeekEnding,   
        CONVERT(varchar(10), MAX(WeekEnding) OVER(), 101) MaxWeekEnding
FROM dbo.Weekly

answered May 2, 2014 at 16:12

Lamak

70.8k12 gold badges118 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to return last date from a single column of dates?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related