Tutorialsteacher

Follow Us

Articles
  • C#
  • C# OOP
  • ASP.NET Core
  • ASP.NET MVC
  • LINQ
  • Inversion of Control (IoC)
  • Web API
  • JavaScript
  • TypeScript
  • jQuery
  • Angular 11
  • Node.js
  • D3.js
  • Sass
  • Python
  • Go lang
  • HTTPS (SSL)
  • Regex
  • SQL
  • SQL Server
  • PostgreSQL
  • MongoDB
  • SQL Server - Get Started
  • Install SQL Server
  • SQL Server Management Studio
  • SQL Server - Windows Authentication
  • SQL Server - Authentication
  • SQL Server - Create New User
  • SQL Server - GRANT/REVOKE Permissions to User
  • SQL Server - Data Types
  • SQL Server - Naming Conventions
  • SQL Server - CREATE Database
  • SQL Server - CREATE Table
  • Add Columns
  • Identity Column
  • Rename Column, Table
  • Drop Columns
  • SQL Server - Schema
  • SQL Server - Tables Relations
  • SQL Server - Primary Keys
  • Modify/Delete Primary Keys
  • SQL Server - Foreign Keys
  • Modify/Delete Foreign Keys
  • SQL Server - Check Constraints
  • SQL Server - Unique Constraints
  • SQL Server - Views
  • Modify/Delete Views
  • SQL Server - Functions
  • SQL Server - Stored Procedures
  • Stored Procedure Parameters
  • SQL Server - Indexes
  • Non-clustered Indexes
  • Modify/Delete Indexes
  • SQL Server - Triggers
  • DDL Triggers
  • LOGON Triggers
  • Enable/Disable Triggers
  • Modify/Delete Triggers
  • SQL Server - Sequence
  • SQL Server - Synonyms
  • SQL Server - IF ELSE Statement
  • SQL Server - Loops
  • SQL Server - Insert Data
  • SQL Server - Update Data
  • SQL Server - Delete Data
  • SQL Server - Select Query
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • ORDER BY Clause
  • SQL Server - Inner Join
  • Left Join
  • Right Join
  • Full Join
  • Self Join
  • Dynamic SQL
  • Built-in Functions
Entity Framework Extensions - Boost EF Core 9
  Bulk Insert
  Bulk Delete
  Bulk Update
  Bulk Merge

SQL Server ISDATE() Function

In SQL Server, the ISDATE() function returns 1 if the input expression is a valid datetime value, else it returns 0.

ISDATE(input_expression)

Parameters

input_expression: It is a string or an expression that can be converted to a string. It must be less than 4000 characters.

Note: The datetime range is 1753-01- 01 to 9999-12-31 and date range is 0001-01-01 to 9999-12-31.

Return Value

Returns integer value 0 or 1. 0 indicates the input expression is not a date and return value. 1 indicates that the input expression is a valid date or datetime value.

If input_expression is a datetime2 value, then ISDATE() returns 0.

Check for Valid Date String

In the following example, the ISDATE() validates whether the specified date string is a valid date or not. It returns 1 if valid date otherwise returns 0.

Example: Validate Date String
SELECT ISDATE ('2022-10-20') AS IsValidDate

Check for Valid DateTime String

In the following example, the ISDATE() function validates whether the specified string is a valid datetime value or not.

Example: Validate DateTime String
SELECT ISDATE ('2022-10-20 10:10:20') AS IsValidDate

In the following example, a string, an integer value, a Boolean and Boolean values are passed to the ISDATE() function which returns a 0 (False).

Example: Invalid Dates
SELECT ISDATE ('Hello') AS IsStrDate, ISDATE(1234) AS IsIntdate, ISDATE('FALSE') AS IsBolDate, ISDATE(0) IsBolintDate

Check for Valid DateTime Format

In the following example, an invalid date format is passed to the ISDATE() function.

Example:
SELECT ISDATE ('2022-20-10') AS IsValidDate

The above query returns 0 because of invalid date format as per the server setting. The return value of ISDATE() depends on the server settings set by SET DATEFORMAT, SET LANGUAGE, and the default language of the server.

To check the current setting of the SQL Server, use the following statement

Example: Server Setting
DBCC useroptions

As you can see, the valid date format for the server is mdy and so the ISDATE() function returned 0. Now, change the date format to dmy, as shown below.

Example: Change Date Format
SET DATEFORMAT dmy;

Now, execute the following SQL again. This time the ISDATE() function returns 1.

Example:
SELECT ISDATE ('2022-20-10') AS IsValidDate

Note: You can change the date format to dmy, ydm, ymd from standard mdy format.

TUTORIALSTEACHER.COM

TutorialsTeacher.com is your authoritative source for comprehensive technologies tutorials, tailored to guide you through mastering various web and other technologies through a step-by-step approach.

Our content helps you to learn technologies easily and quickly for learners of all levels. By accessing this platform, you acknowledge that you have reviewed and consented to abide by our Terms of Use and Privacy Policy, designed to safeguard your experience and privacy rights.

[email protected]

ABOUT USTERMS OF USEPRIVACY POLICY
copywrite-symbol

2024 TutorialsTeacher.com. (v 1.2) All Rights Reserved.