SQL ISDATE

The SQL ISDATE function is used to determine whether an expression is a valid date format. It returns 1 (true) if the expression is a valid date and 0 (false) otherwise. The ISDATE function is primarily available in SQL Server and is commonly used in data validation scenarios.

In this tutorial, we will explore the ISDATE function, its syntax, and practical use cases with step-by-step examples.

Syntax of SQL ISDATE Function

The basic syntax of the SQL ISDATE function is:

</>
Copy
SELECT ISDATE(expression);

Parameters:

  • expression: The value or column to check whether it is a valid date format.

Return Values:

  • Returns 1 if the value is a valid date.
  • Returns 0 if the value is not a valid date.

Examples of SQL ISDATE Function

1. Checking a Valid and Invalid Date

Let’s check different values to see whether they are valid dates:

</>
Copy
SELECT ISDATE('2025-02-12') AS ValidDate1,
       ISDATE('12-Feb-2025') AS ValidDate2,
       ISDATE('Hello World') AS InvalidDate,
       ISDATE('2025/13/05') AS InvalidDate2;

Explanation:

  • '2025-02-12' and '12-Feb-2025' are valid date formats, so they return 1.
  • 'Hello World' is not a valid date, so it returns 0.
  • '2025/13/05' has an invalid month (13), so it returns 0.

2. Using ISDATE in a Table with Names

Let’s create a users table to demonstrate the ISDATE function on stored data.

</>
Copy
CREATE TABLE users (
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(50),
    birthdate VARCHAR(50)  -- Stored as string for validation
);

Insert some sample data:

</>
Copy
INSERT INTO users (name, birthdate)
VALUES 
('Arjun', '1995-08-15'),
('Ram', '2001-12-05'),
('Priya', 'InvalidDate'),
('Meera', '2023/02/30');

Now, let’s use ISDATE to check which users have valid birthdates:

</>
Copy
SELECT name, birthdate, 
       ISDATE(birthdate) AS IsValidBirthdate
FROM users;

Explanation:

  • '1995-08-15' and '2001-12-05' are valid dates, so they return 1.
  • 'InvalidDate' and '2023/02/30' are invalid, so they return 0.

Conclusion

The SQL ISDATE function is used for validating date values in a database. In this tutorial, we covered:

  1. How ISDATE works and its return values.
  2. Checking different date formats to determine validity.
  3. Using ISDATE in a table to validate stored data.