Link to home
Start Free TrialLog in
Avatar of Ernesto
ErnestoFlag for Mexico

asked on

Get max date from a varchar in SQL query!

I need to get the max date from  a varchar of a table in format 103  dd/mm/YYYY

whats the query in sql
please you experts!
ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Russ Suter
Russ Suter

SELECT
  CONVERT(DATETIME, [columnName], 103) as MaxDate
FROM
  [tableName]
WHERE
  [columnName] IN
  (
  SELECT
    MAX([columnName])
  FROM
    [tableName]
  )

Open in new window

Note, if two or more rows in the table share the exact same DATETIME value you will get more than one row using this method.
Maybe I am missing something but MAX() from varchar column containing the date in given format does not calculate the maximum date value.
>I need to get the max date from  a varchar of a table in format 103
(1) Please give us a data mockup of what these varchar's are, as things can get ugly real quick.
(2) Just to state the obvious dates should be stored in date column data types and not as varchar's, as that invites errors to occur when data is entered in a non-date format, and then a later query attempts to convert the value to a date and then fails.
Avatar of Ernesto

ASKER

Thank you guys !
regards