Handle ISDATE() with care

Now-a-days it becomes common practice for newbies to use varchar datatype to store dates. My first advice is to use proper DATETIME datatype. Sometimes it is preferred to use varchar when importing dates from other systems(to avoid any errors if the source dates are in different format). In that case it is required to see if values are actually dates. To check if a value is proper date, it is suggested to use ISDATE()

eg

select isdate('2007-10-12'),isdate('12-12-2006'),isdate('March 12, 2007')

Now it should be noted that the following return 1


select isdate(2007),isdate('2007')

becuase  the values in a ISDATE functions are first casted to datetime

              

select cast(2007 as datetime)                                        
------------------------------------------------------
1905-07-01 00:00:00.000

 

select cast('2007' as datetime)                                               
------------------------------------------------------
2007-01-01 00:00:00.000

Which are valid dates

So if varchar datatype is used to stored formatted dates (ex YYYYMMDD), to check if it is valid date, you need not only to use ISDATE() function, but also use LEN() function

Consider the following set of data and see the difference


 

declare @dates table (dates varchar(8))

insert into @dates(dates)
Select '20071201' union all
Select '20071111' union all
Select '2007' union all
Select 2007 union all
Select '1800'

select dates from @dates
where ISDATE(dates) =1

--Result
dates   
--------
20071201
20071111
2007
2007
1800

--Result

select dates from @dates
where ISDATE(dates) =1 and LEN(dates)=8


dates   
--------
20071201
20071111

Published 24 September 2007 09:39 by Madhivanan
Filed under: ,

Comments

# re: Handle ISDATE() with care

19 February 2008 11:14 by Harsh Athalye

What about the separator characters like '/'?

In that case this validation wouldn't be sufficient.

# re: Handle ISDATE() with care

19 February 2008 14:25 by Madhivanan

Harsh

It depends on the format you stored

Can you give me some examples?

# Converting varchar to datetime | keyongtech

Pingback from  Converting varchar to datetime | keyongtech