how to get the max date in the table

• Guvera

  SSC-Addicted

  Points: 426

  More actions

  May 8, 2023 at 10:10 am

  #4185964

  I have a  below table with the values

  cscode      StartDate       EndDate

  0011    5/9/2022      10/1/2022

  0011    6/17/2022    1/1/2023

  0011    5/9/2022      10/1/2022

  The date column is varchar. I need to take the latest mm/dd/yyyy using EndDate. For ex, in the above table I need to take 01/01/2023. Also I need to select cscode and startdate. So my output looks like below

  cscode      StartDate       EndDate

  0011    6/17/2022    1/1/2023

  I tried the below query

  select max(convert(date, EndDate,101)) as EndDate, StartDate from table1 where cscode = '0011' group by StartDate

  I am getting the below output

  EndDate    StartDate

  10/1/2022     5/9/2022

  1/1/2023        6/17/2022

  I need only one result of the latest mm/dd/yyyy. Please correct me if i did anything wrong in the query.

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  May 8, 2023 at 10:19 am

  #4185973

  Here is one way

  SELECT TOP (1) <column converted to date>, other columns
  FROM table
  ORDER BY <column converted to date> DESC

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin