Converting Varchar to Date and date Comparison

Question

I am converting two Date columns to find the most recent one

SELECT ISNULL(CONVERT(varchar(10),REPLACE('10-07-2015','/','-'), 103),'01-01-1900'),
ISNULL(CONVERT(varchar(10),REPLACE('10/7/2015','/','-'), 103),'01-01-1900'),
     CASE 
        WHEN ISNULL(CONVERT(varchar,REPLACE('10-07-2015','/','-'), 103),'01-01-1900') = ISNULL(CONVERT(varchar,REPLACE('10/7/2015','/','-'), 103),'01-01-1900')
          THEN '10-07-2015'
    END

My issue is some dates missing leading Zero in Day or Month and comparison is giving false results. Is there a better way to handle this? Other issue is one column has date with '/' and other have with '-'

Currently case is only checking on '=' but will add more to get the most recent

Why on earth are you storing DATE values in a VARCHAR column. That is a **really**, really bad idea — , Nov 14 '18 at 11:12
Types exist for a reason, you should use them. In this case the Date or DateTime2 type would be the most appropriate choice, not varchar. See also [When to use VARCHAR and DATE/DATETIME](https://stackoverflow.com/a/4759039/1260204) — Igor, Nov 14 '18 at 11:16

LukStorms · Accepted Answer · 2018-11-14T13:14:32.860

You can just convert those 2 varchars to the DATE type, then compare them.

You can find the date/datetime styles here

For those DD/MM/YYYY datestamps the 103 style would fit.

And to calculate the most recent between them, just wrap it in a CASE.

Example snippet:

declare @T table (
  id int identity(1,1) primary key, 
  datestamp1 varchar(10), 
  datestamp2 varchar(10)
);

insert into @T (datestamp1, datestamp2) values 
 ('5/9/2018','17/9/2018')
,('9-10-2018','16-10-2018')
,('15-10-2018','13-10-2018')
;

SELECT *,
TRY_CONVERT(DATE, datestamp1, 103) as date1, 
TRY_CONVERT(DATE, datestamp2, 103) as date2,
CASE 
WHEN TRY_CONVERT(DATE, datestamp1, 103) >= TRY_CONVERT(DATE, datestamp2, 103) THEN datestamp1
WHEN TRY_CONVERT(DATE, datestamp2, 103) IS NULL THEN datestamp1
ELSE datestamp2
END AS MostRecentDatestamp
FROM @T;

Returns:

id  datestamp1  datestamp2  date1       date2       MostRecentDatestamp
1   5/9/2018    17/9/2018   2018-09-05  2018-09-17  17/9/2018
2   9-10-2018   16-10-2018  2018-10-09  2018-10-16  16-10-2018
3   15-10-2018  13-10-2018  2018-10-15  2018-10-13  15-10-2018

Converting Varchar to Date and date Comparison

1 Answers1