IsDate() - function

Question

Post reply

IsDate() - function

tlitschko

Old Hand

Points: 351
More actions
March 26, 2002 at 12:35 pm

#77962

I need to check whether a string represents a valid date and, if so, convert it to a datetime value in the same table.
I use:
update <table>
set BuchungZeit = Convert(datetime,<col>,112)
where IsDate(SubString(<col>,1,4)+'-'+
SubString(<col>,5,2)+'-'+
SubString(<col>,7,2)) = 1
I have a problem with IsDate() being nondeterministic: testing the where-clause against consecutive manipulations of some values of the column <col> does not always yield correct results. The SQL-Documentation states:
"Deterministic only if used with the CONVERT function, the CONVERT style parameter is specified and the style parameter is not equal to 0, 100, 9, or 109."
But how can i use IsDate() with Convert() without receiving Error 242 for an invalid date format in <col>?
Since I use both SQL Server 7 and SQL Server 2000, writing a udf is not really a solution.
thanks
Thomas

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

awmuse SSC Enthusiast Points: 142 More actions · Answer 1

It seems that this would work:

update <table>

set BuchungZeit = cast(<col> as datetime)

where IsDate(<col>) = 1

You are checking the first 9 characters of <col> in your where clause, yet you aren't as discriminating in your set command. If your column length could potentially exceed 9 characters then this would work:

update <table>

set BuchungZeit = cast(left(<col>,9) as datetime)

where IsDate(left(<col>,9)) = 1

tlitschko Old Hand Points: 351 More actions · Answer 2

thanks awmuse,

being less discriminate really works. But the problem is not the difference between the set command and the where clause (since when checking the where clause I used "select ...").

The date-column <col> to be checked has format style 112 (yyyymmdd) and from the SQL2000 documentation I concluded that I need to supply style 101 or 120 to IsDate() in order to get correct results. Rather, supplying the <col> as is in style 112 works nicely.

Remains the question why "select IsDate(20000228)" results correctly in 1, but both "select IsDate('02/28/00')" and "select IsDate('2000-02-28')" result in 0?

Any ideas?

regards, Thomas

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Oddly enough all 3 returned 1 for me. What language are you setup with for date formating?

Sorry more specifically set default for me these all return 1. If I do SET DATEFORMAT

mdy = 1,1,1

dmy = 1,0,0

ymd = 1,0,1

ydm = 1,0,0

dym = 1,0,0

myd = 1,0,1

with your values.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Edited by - antares686 on 03/27/2002 04:50:52 AM

tlitschko Old Hand Points: 351 More actions · Answer 4

You are right, thanks.

The DATEFORMAT setting determines the behaviour; mine is 'dmy'.

But still, IsDate() seems somewhat weird:

why does "select IsDate(20000228)" and "select IsDate('20000228')" both return 1 with a setting of 'dmy'?

Did anyone understand this nondeterministic stuff for IsDate() in the documentation?

Thomas

Antares686 SSC Guru Points: 125444 More actions · Answer 5

I believ in the case on IsDate it actually performs a type change and looks for a return error which means cannot be formatted to that datatype. I am have always viewed nondeterministic functions as those that are effected by environmental differences suc as the system date format or language setting.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)