CODEWITHSUNDEEP
sql-servert-sql
Syed Imran Shah
Syed Imran Shah

Reputation: 43

Compare a date with specific date format

I have column of type varchar in a table and have different type of date format in it. I want to compare it with specific format of date. If it is true then those rows should be returned only. Format of the date is 'yyyymmdd'

e.g., 20200831 = 'yyyymmdd'

dates are in below format

20200831
31/Aug/2020
08-31-2020
2020-08-31

These are few date format which are present in the table.

Upvotes: 0

Views: 804

Answers (3)

Zhorov
Zhorov

Reputation: 29933

If I understand the question correctly, a possible solution is the following statement:

SELECT DateText
FROM (VALUES
   ('20200831'),
   ('31/Aug/2020'),
   ('08-31-2020'),
   ('2020-08-31')
) v (DateText)
WHERE CONVERT(varchar(8), TRY_CONVERT(date, DateText), 112) = DateText

Result:

DateText
20200831

Upvotes: 2

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

SELECT *
FROM tableName
WHERE ISDATE(dateCol) = 1 AND ISNUMERIC(dateCol) = 1;

ISDATE() function returns 1 if column value is valid SQL Server date and ISNUMERIC() returns 1 if column value is valid number, so 20200831 satisfies both conditiion.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

If you are using SQL Server 2012 or later, then the TRY_CONVERT function is one option here:

SELECT
    dt,
    TRY_CONVERT(datetime, dt) AS dt_out
FROM yourTable
WHERE
    TRY_CONVERT(datetime, dt) = '2020-08-31';

screen capture from demo link below

Demo

Data:

WITH yourTable AS (
    SELECT '20200831' AS dt UNION ALL
    SELECT '31/Aug/2020' UNION ALL
    SELECT '08-31-2020'  UNION ALL
    SELECT '2020-08-31'
)

Upvotes: 0

Related Questions