By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table dateformats(datevalue varchar(50))
insert dateformats
values ('01-january-2019'),('31-JAN-2019'),('2019-01-01'),
('16-07-2019'),('20-12-2019'),('16-10-2019'),('16-AUG-2019'),('16/07/2019'),('07/16/2019'),('01-jan-2019'),('01-january-2019')
SET DATEFORMAT dmy
SELECT formats into #temp FROM (
SELECT
max(
CASE WHEN isdate(datevalue) = 1 then
(CASE WHEN CAST(datevalue AS VARCHAR(50)) = CAST(FORMAT(CAST(datevalue AS DATE),'dd-MM-yyyy') AS VARCHAR(50)) THEN 'dd-MM-yyyy' ELSE '' END)
ELSE '' END )
AS formats from dateformats
UNION
SELECT
max(
CASE WHEN isdate(datevalue) = 1 then
(CASE WHEN CAST(datevalue AS VARCHAR(50)) = CAST(FORMAT(CAST(datevalue AS DATE),'dd-MMMM-yyyy') AS VARCHAR(50)) THEN 'dd-MMMM-yyyy' ELSE '' END)
ELSE '' END )
AS formats from dateformats
UNION
SELECT
max(
CASE WHEN isdate(datevalue) = 1 then
(CASE WHEN CAST(datevalue AS VARCHAR(50)) = CAST(FORMAT(CAST(datevalue AS DATE),'dd/MM/yyyy') AS VARCHAR(50)) THEN 'dd/MM/yyyy' ELSE '' END)
ELSE '' END )
AS formats from dateformats
UNION
| formats |
|---|
| dd-MM-yyyy |
| dd-MMM-yyyy |
| dd-MMMM-yyyy |
| dd/MM/yyyy |
| MM/dd/yyyy |