IsDate Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

IsDate Function

Author

Topic

iloveorangesoda
Starting Member

30 Posts

Posted - 2008-03-10 : 09:36:51

I am trying to valid dates in a field within a table and I am getting problems when using the IsDate Function.

For example I would call the following code:

UPDATE Shared.dbo.t_UnTyped
SET Failed = 1,
FailedReason = IsNULL(FailedReason,'') + 'Incremental Date is not a valid date, '
WHERE (IsDate(dbo.ConvertDate(Field73)) = 0

ConvertDate Function:

CREATE FUNCTION ConvertDate (@Input varchar(60))
RETURNS datetime
AS
BEGIN

DECLARE @Output datetime

IF ISDATE(@Input) = 0
BEGIN
SET @Output = null
END
ELSE
BEGIN
SET @Output = CAST(@Input AS datetime)
END

RETURN @Output

END

Now if the date is 20080229 this comes back as a valid date.
However if the date is 20090229 this comes back as an invalid date.

Can anyone shed some light on what is going wrong?

Thanks

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-10 : 09:38:50

but 29th Feb 2009 isn't a valid date, so that's right eh?

Em

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-10 : 09:39:16

Because 2009 is not a leap year!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-10 : 09:49:58

UPDATE Shared.dbo.t_UnTyped
SET Failed = 1,
FailedReason = IsNULL(FailedReason,'') + 'Incremental Date is not a valid date, '
WHERE dbo.ConvertDate(Field73) is ~~not~~ null

E 12°55'05.25"
N 56°04'39.16"

iloveorangesoda
Starting Member

30 Posts

Posted - 2008-03-10 : 09:54:56

oops :)

cheers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-10 : 09:59:50

Make sure you learn about ISDATE() function fully before using it. It will return 1 for any input which it can convert to date. For example, ISDATE(2009). So based on your requirement you might need to use additional condition checks for proper validation of dates.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-13 : 06:59:39

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-03-13 : 07:32:34

You know the function is unnecessary, right?


UPDATE Shared.dbo.t_UnTyped
SET Failed = 1,
FailedReason = IsNULL(FailedReason,'') + 'Incremental Date is not a valid date, '
WHERE IsDate(Field73) = 0

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources