Finding Max of dates in SQL Query-CodePudding

CType	Material	CoCd	Plnt	SOrg	SoldTo	ShipTo	Customer	Vendor	SerProv	POrg	Year	per	UoM	NSapRel	Amount	Crcy	ChangedOn	Time	Changedby	SAPdate	SAPTime	SAPUser
PB00	775515	100	GA					7500000678		PO04	2022	100	CAS		9,299.36	BRL	15.02.2022	13:55:16	User1	15.02.2022	13:56:15	User1
PB00	775515	100	GA					7500000678		PO04	2022	100	CAS		5,907.42	BRL	28.01.2022	18:45:45		02.02.2022	12:32:18	SAPPO_DEP_AZ

I have written a query for the above table, to get max date of these two rows, Unfortunately, it returns as '28.01.2022' every time.

Here is my query.

SELECT
       CType,
       Material,
       CoCd,
       Plnt,
       [Customer],
       SOrg,
       [Vendor],
       max_date = MAX(ChangedOn) OVER (
          PARTITION BY
              CType,
              Material,
              CoCd,
              Plnt,
              [Customer],
              SOrg,
              [Vendor]) 
FROM
      Calc
WHERE
          CType='PB00'
      AND
          Material='775515'
      AND
          Plnt='GA'

Expected output: for MAxDate column is '15.02.2022'

All columns in Varchar(500), so I tried following query as well,

SELECT
       CType,
       Material,
       CoCd,
       Plnt,
       [Customer],
       SOrg,
       [Vendor],
       MAX(cast(ltrim(rtrim(ChangedOn)) as date)) AS max_date
FROM
       Calc
WHERE
          CType='PB00'
   AND
          Material='775515'
   AND
          Plnt='GA'
GROUP BY
          CType,
          Material,
          CoCd,
          Plnt,
          [Customer],
          SOrg,
          [Vendor]

Showing error 'Conversion failed when converting date and/or time from character string.'

Is anything wrong here?

CodePudding user response：

You could use the CONVERT or TRY_CONVERT function to convert the varchar date to date datatype inside the max function, But it would be better to store date values as a date to avoid such problems.

Try the following:

SELECT CType,Material, CoCd, Plnt,  [Customer], SOrg, [Vendor],
       max_date = MAX(TRY_CONVERT(DATE, ChangedOn, 104)) OVER 
       (PARTITION BY CType,Material, CoCd, Plnt, [Customer], SOrg, [Vendor])    
FROM Calc where CType='PB00' and Material='775515' and Plnt='GA'

See a demo.