Isdate Function in SQL Evaluates Invalid Dates as Valid

IsDate Function in SQL evaluates invalid dates as valid

I do a lot of data conversion work and here is a function that I created and use it practically everyday to weed out the bad dates:

CREATE FUNCTION dbo.fnCheckDate
(@InDate nvarchar(50))
RETURNS DATETIME
AS
    BEGIN
        declare @Return DATETIME

        select @return = CASE WHEN ISDATE(@InDate) = 1
                            THEN CASE WHEN CAST(@InDate as DATETIME) BETWEEN '1/1/1901 12:00:00 AM' AND '6/6/2079 12:00:00 AM'
                                    THEN @InDate
                                    ELSE null
                                    END
                            ELSE null
                            END
        return @return
    END
GO

Results:

SELECT dbo.fnCheckDate('07/001/2012') --> Returns 2012-07-01 00:00:00.000
SELECT dbo.fnCheckDate('2012-07-002') --> Returns 2012-07-01 00:00:00.000
SELECT dbo.fnCheckDate('007/002/2012') --> Returns 2012-07-01 00:00:00.000
SELECT dbo.fnCheckDate('00/002/2012') --> Returns Null
SELECT dbo.fnCheckDate('006/031/2012') --> Returns Null
SELECT dbo.fnCheckDate('') --> Returns Null

Isdate Function Treating This 3/25/013 as valid date

http://technet.microsoft.com/en-us/library/ms187347.aspx

isdate does accept both m/d and d/m order (but behaviour depends on DATEFORMAT value).

ISDATE producing error for DATE (datatype) value

ISDATE ( expression )

Arguments

expression
Is a character string or expression that can be converted to a character string. The expression must be less than 4,000 characters. Date and time data types, except datetime and smalldatetime, are not allowed as the argument for ISDATE.

Source : https://learn.microsoft.com/en-us/sql/t-sql/functions/isdate-transact-sql

Checking for invalid date in SQL Server Stored Procedure

Since @f2 is a datetime it will never hold an invalid date

but just for fun, this is how you would check it

IF ISDATE(@f2) =0
SET @f2= NULL

But like I said this can never be true, if you pass Jan 1 1600 to the proc it will blow up and never execute

if you are on SQL Server 2008 you can use datetime2

Here is an example if you can't change the datetime column, you can still use datetime2 for checking

declare @d datetime2
select @d = '16000101'

select @d,
    convert(varchar(8),@d,112),
    isdate(convert(varchar(8),@d,112))  -- not a valid datetime

isdate and new zealand returning false

Ideally you would control the string format better than that. You shouldn't care whether the culture is EN-NZ or anything else if you pass in a standard format, such as:

SELECT ISDATE('20120621 16:11'); -- will never fail regardless of locale/language/DMY

If you need to allow people to enter dates in any format they wish, then you have to deal with it in various ways. For example:

SELECT ISDATE(CONVERT(DATETIME, REPLACE('2012-06-21 4:11 P.M.', '.', ''), 120));

How to Check Valid Date Time from Sql Table

Select *
From Table
Where
  ( '2016-05-19 04:23:00.000' <= dateColumn )
  And ( dateColumn < '2016-05-19 04:50:00.000' )

IsDate in TSQL Issue

Easiest way might be to add "00:" when the length is 5 characters.

e.g. :

declare @str varchar(100)
set @str = '25:01'
-- if length is 5, interpret as  00:mi:ss
set @str = (CASE WHEN LEN(@str)=5 THEN '00:' + @str ELSE @str END)
select ISDATE(@str) -- shows 1
select cast(@str as time)  -- shows 00:25:01.000

Does IsDate function in SQL Server treat 201301 as YYYYMM or YYMMDD?

1. ISDATE is interpreting a six character string as yymmdd.

2. It is dependent on the configured cutoff Server Configuration Option. Default is 2049, so years ending in < 50* is 20xx and years >= 50 is 19xx.

3. Your test code is dealing with integers and is implicitly cast to a string when passed as an argument to isdate with no leading zeros.

Valid Date Checks in Oracle

Yes, if you know the format and with little plsql.

Let's say you have the date in format 'yyyy-mon-dd hh24:mi:ss'.

create function test_date(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'yyyy-mon-dd hh24:mi:ss') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;

Now you can:

select your_date_col, test_date(your_date_col)
from your_table;

---

Related Topics