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
[...] Aug 11th, 2007 by pinaldave I received quite a good feedback about my post about SQL SERVER – Validate Field For DATE datatype using function ISDATE() [...]
Just be aware that the isdate() will not work with the new “date” datatype in SQL 2008
–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
FYI, ISDATE() is not reliable
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/hadle-isdate-with-care.aspx
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?
Make sure to read this post
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx
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
Hello Ashutosh,
How many row should this query return?
Because the record that you mention does not pass the criteria.
If there is other issue than please clarify.
Regards,
Pinal Dave
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.
IsDate() is not reliable.
select isDate(’01/01/9607′) — returns 1
select convert(datetime,’01/01/9607′,1) — fails
Yes. It is not reliable
Refer this for more informations and to knwo how to use it effectively
http://beyondrelational.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx
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
Post the code that caused the error
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 ?
select column_name, data_type from information_schema.columns
where table_name=’your_table’
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.
Yes. It should have updated
hi all,
pls vote this is important,
https://connect.microsoft.com/sql/feedback/ViewFeedback.aspx?FeedbackID=327335&wa=wsignin1.0
Thanks
Tharindu Dhaneenja
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)’
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?
Use isdate function
if isdate(date)=1 and len(date)=10
–valid date
hi i want the all validations for the given date
Explain the validation rules
Hi
Sir
Use isdate function