SQL: Check to see if field is a date and also between two dates-CodePudding

I am trying to write a SQL query that will check the data is between two dates, but the date is stored as varchar in the table. It is possible that the date is not in the right format. Below is the query:

SELECT DISTINCT t.id, CONVERT(datetime,c.txt_date) as date
FROM table t
WHERE ISDATE(t.txt_date) = 1
    AND CONVERT(datetime,t.txt_date)BETWEEN @BeginDate AND @EndDate

The variables @BeginDate and @EndDate are declared as date datatypes at the beginning of the script.

I get this error: Conversion failed when converting date and/or time from character string.

How can I check to see if the value in the field, txt_date, is date first and also check to see if the value is between @BeginDate and @EndDate? Can I do this in 1 SQL script?

CodePudding user response：

Have you tried using the 'style' parameter of the CONVERT function? If you think there might be non-date strings in the data, you can use TRY_CONVERT which will return NULL if the conversion fails and supply a default value in its place.

COALESCE(TRY_CONVERT(t.txt_date), "default value")

CodePudding user response：

Try with :

CAST(c.txt_date AS DATETIME) as date