SQL SERVER – Validate Field For DATE datatype using function ISDATE()

This article is based on the a question from Jr. Developer at my company. He works with the system, where we import CSV file in our database. One of the field in the database is DATETIME field. Due to architecture requirement we insert all the CSV fields in the temp table which has all the fields VARCHAR. We validate all the data first in temp table (check for inconsistency, malicious code, incorrect data type) and if passed validation we insert them in the final table in database.

We always checked DATETIME field for incorrect data type. One of the user input date as 30/2/2007. The date was sucessfully inserted in the temp table but while inserting from temp table to final table it crashed with error. We had now task to validate incorrect date value before we insert in final table. Jr. Developer asked me how can he do that? We check for incorrect data type (varchar, int, NULL) but this is incorrect date value. Regular expression works fine with them because of mm/dd/yyyy format.

After long history of the problem the solution is very simple. We now use ISDATE() function to validate if the date is valid.

Examples:

----Invalid date
SELECT ISDATE('30/2/2007')
RETURNS : 0 (Zero)
----Valid date
SELECT ISDATE('12/12/20007')
RETURNS : 1 (ONE)
----Invalid DataType
SELECT ISDATE('SQL')
RETURNS : 0 (Zero)

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL

39 thoughts on “SQL SERVER – Validate Field For DATE datatype using function ISDATE()

  1. Pingback: SQL SERVER - UDF - Validate Integer Function Journey to SQL Authority with Pinal Dave

  2. –TheDate is a legacy varchar(10) column that allowed users to enter a date, 2/29/07, 02-29-2007, Today, Daily, etc…
    –Why does ISDATE(TheDate) = 1 not restrict the result set before it does the cast?
    –Conversion failed when converting character string to smalldatetime data type

    SELECT
    ID,
    UserID,
    TheDate
    FROM
    Table1
    WHERE
    ISDATE(TheDate) = 1
    CAST(TheDate AS DATETIME) >= @Start_Date

    –Instead I have to do this…

    SELECT
    ID,
    UserID,
    TheDate
    FROM
    Table1
    WHERE
    ISDATE(TheDate) = 1
    AND TheDate ‘Daily’
    AND TheDate ‘Today’
    AND CAST(TheDate AS DATETIME) >= @Start_Date

    Like

  3. I ran into a similar problem that ran a little deeper.

    I was converting some data that came from one of our Canadian clients and our office is in the U.S.

    The problem I had with the IsDate function is that Canadian dates stored as VARCHAR(8) usually assume the French date style of DD/MM/YY. When trying to convert them, they would fail if the date was invalid.

    Using IsDate from and American computer transposes the month and day field and also returns 0 for quite a few legit dates. As a result of this, I was unable to check for invalid dates.

    I had to wrap most of my sql script inside a couple lines that temporarily change the local language to get the correct results from the IsDate function.

    SET LANGUAGE French

    –my code

    SET LANGUAGE English

    Is there any other way to do this without changing the language?

    Like

  4. hi

    i have following records in this my datetime table

    startdate enddate
    2009-12-29 00:00:00.000 2010-02-28 00:00:00.000

    when i run the following query :

    select * from datetime where startdate>=’2010-01-31′ and enddate<='2010-02-15'

    it will 0 row return : how?

    Please help me
    Thanks in advance

    Like

    • Please be aware that the isdate() function, although is for “validating” date formats, it, counterintuitively, cannot validate a true date data type if one happens to land as its parameter. It may sound like a “duh” for the dude at Microsoft who made such function, but then this post would never exist if us users were gifted with such Vulcanian logic.

      If, for example, you’re looking for a “good” date (not null and convertible to a valid date) between two columns, one a date type, the other a varchar:

      select isdate(isnull(datefield, textfield)) — ERROR(!)
      ( simpler example: select isdate(datefield) — ERROR )

      It will throw an error since the “datefield” column is an actual datetime type, and isdate() can only evaluate text strings (char, varchar, nvarchar, etc…) To fix it: just wrap the datefield in a cast function that converts it to varchar:

      select isdate(isnull(cast(datefield as nvarchar), textfield)) –> 1 or 0

      blue plate example: select isdate(cast(datefield as nvarchar)) –> 1 or 0

      Done.

      Hope this helps!

      Like

  5. There are some typos in the blog,

    Just to make things clear ..
    First – ISDATE Function validates a Date and returns a 1 when it comes across a valid date else returns 0

    In the Blog the 2nd example still has an Invalid Date.

    Like

  6. IsDate() is not reliable.

    select isDate(’01/01/9607′) — returns 1
    select convert(datetime,’01/01/9607′,1) — fails

    Like

  7. when inserting a row into the table which has a column with datatype smalldatetime im getting the following error

    Conversion failed when converting character string to smalldatetime data type

    how can i fix this error???

    thanks

    Like

  8. Hi, I actually have a wider question: say we have a table where all columns are VARCHARs and we need to determine type of data in each column. I do understnad that result may not be 100% properly guaranteed but how would you solve this task ?

    Like

  9. BTW in SQL Server 2008 there is a new datatype DATETIME2 which supports the dates from 0001-01-01 through 9999-12-31. But Microsoft did not update the ISDATE() function to support this date range. no even 2008 R2. This is funny.

    Like

  10. Did you write code on the fly or checked it by executing your script.

    —-Valid date
    SELECT ISDATE(’12/12/20007)’
    RETURNS : 1 (ONE)

    Incorrect syntax near ’12/12/20007)’

    Like

  11. Fairly new to native sql. The user is sending a date defined as char(10). I need to check this to make sure it is coming in the format of yyyy-mm-dd. How do I do this?

    Like

  12. Thanks for such an informative website :)
    I have a problem where dates have been saved in a varchar field, in various formats, some with separators and some without, and some entries in the field are not dates at all, they might be someones name or something like that.

    I’m trying to select all records with valid dates between 2 dates, and I get errors where something like “204 4000″ fails conversion to an integer.

    I’ve selected all where isdate(date) = 1 into a temporary table, then select from that table all dates between the dates I want.

    From reading the comments here, I can see that null or empty fields will be counted as valid dates, but are really invalid dates.

    Is there a better way of selecting records within a particular date range where the dates are valid?

    Like

  13. I wrote a custom function to replace isDate() called isValidDate()
    Enjoy:
    This function will return a 1 if the string parameter will convert to a valid dd/mm/yyyy (1753-9999) datetime and 0 if not.
    ********************************************************************/
    CREATE FUNCTION [dbo].[FN_isValidDate](@inputstring VARCHAR(4000))
    RETURNS int
    BEGIN
    DECLARE @output int

    SELECT @output = (CASE WHEN (@inputstring LIKE ‘__/__/____’)
    AND (dbo.cust_wFN_isReallyNumeric(replace(@inputstring,’/’,”)) = 1)
    AND (RIGHT(@inputstring,4) between ‘1753’ and ‘9999’) –Theoretically 1753 is the lowest possible year.
    AND (substring(@inputstring,4,2) between ’01’ and ’12’)
    THEN 1
    — WHEN @inputstring is null
    — THEN 1
    ELSE 0 END)
    RETURN @output

    Like

    • Oh I forgot, that uses my isReallyNumeric() function as well, which is here:
      /********************************************************************
      Function Purpose:
      This function will return a 1 if the string parameter contains ONLY
      numeric (0-9) digits and will return a 0 in all other cases.
      ********************************************************************/
      ALTER FUNCTION[dbo].[cust_wFN_isReallyNumeric](@inputstring VARCHAR(4000))
      RETURNS int
      BEGIN
      DECLARE @output int

      SELECT @output = (CASE WHEN NULLIF(@inputstring,”) — If string is empty
      NOT LIKE ‘%[^0-9]%’ — and LIKE numbers 0-9 (NOT LIKE double negative needed here)
      THEN 1 ELSE 0 END) — then return int 1

      RETURN @output
      END

      Like

  14. Hi guys IsDate(‘01232011′) function is not working with MMDDYYYY format?
    it always return 0 how could I check date format MMDDYYYY

    Like

  15. Solved
    select convert(varchar(10),convert(date, convert(datetime(’02-015-2013′)), 101)
    I wanted a character string as part of a case statement

    Like

  16. Pingback: SQL SERVER – Weekly Series – Memory Lane – #037 | Journey to SQL Authority with Pinal Dave

  17. Hi ,
    I have an requirement like this.Before inserting the data from one table to another table certain validations has to be passed.Kindly Suggest me solution for this.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s