Need a datetime validate function

Question

Post reply

Need a datetime validate function

Andervin

Valued Member

Points: 61
More actions
May 2, 2003 at 2:25 pm

#80014

I'm validating raw data in a migration process and need a datetime validation function. Can anyone help?
Developer Developer Developer Developer Developer Developer Developer *jumps around like a moron* Developer Developer

Developer Developer Developer Developer Developer Developer Developer *jumps around like a moron* Developer Developer

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

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

mimorr SSCarpal Tunnel Points: 4850 More actions · Answer 1

select * into newtable

where isdate(hopefully_date_field) = 1

Insert into newtable

select field1, case field2

when isdate(hopefully_date_field) = 1 THEN hopefully_date_field

else null

end

Either of these help?

Michelle

Andervin Valued Member Points: 61 More actions · Answer 2

I was going to use Isdate() but it doesn't work on Datetime types, does it?

Developer Developer Developer Developer Developer Developer Developer *jumps around like a moron* Developer Developer

mimorr SSCarpal Tunnel Points: 4850 More actions · Answer 3

create table admin.dbo.temptable

(textdatetime char(16))

insert into admin.dbo.temptable

values('010203 12:00')

alter table admin.dbo.temptable

add field2 datetime

update admin.dbo.temptable

set field2 = textdatetime

where isdate(textdatetime) = 1

select * from admin.dbo.temptable

textdatetime

010203 12:00

field2

2001-02-03 12:00:00.000

It works in this example

Michelle

Andervin Valued Member Points: 61 More actions · Answer 4

Thanks, but it appears that Isdate() does indeed work on a datetime column.

Developer Developer Developer Developer Developer Developer Developer *jumps around like a moron* Developer Developer

jmadren SSCrazy Points: 2055 More actions · Answer 5

I'm unclear of what you're trying to do here. You say you need to validate datetime values, but then you mention validating an existing datetime column. If the datetime value was invalid to begin with, it couldn't have been assigned to an actual datetime column. Isdate() will always return 1 for an actual datetime column or variable, unless its value is NULL. So, are you trying to validate a "text" representation of a datetime value before assigning it to an actual datetime column?

Jay Madren