How to check if a datetime is a datetime?

[TonyScarpelli]

I have a character field where the most of the dates are in the format 'Mar 8 2011 12:00AM', however, there are sometimes dates like this:
'April 1, 2009' or '06-2013' or '09/04/2009'.

I am trying to check to see if the date is a DATETIME and if it isn't to either skip the record or convert it into a DATETIME format.

Can I do something like this in a select statement:
IF CAST(MyTable.FIELD5 AS DATETIME) = DATETIME()
CAST(AIMS.EXPD.FIELD5 AS DATETIME) < @StartDate
ELSE
NewDate = CONVERT_TO_DATETIME(FIELD5)
IF NewDate < @StartDate

Thanks for any help on this.




Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[RiverGuy]

Use the ISDATE() function.

 

[bborissov]

Why not just use the appropriate field type?
Use datetime type and you shouldn't bother for such problems anymore.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[TonyScarpelli]

Borislav, the table came over from a conversion, the field was a text field, people started entering dates as they saw fit, even though they were told how to enter the correct date format, the application isn't mine, the table isn't mine, and so I just have to deal with such things. I can't change it.

At least the dates look like dates.

Thanks.


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[bborissov]

I was afraid to hear something like that. You can use ISDATE() function, but converting depends of SET DATEFORMAT or the last parameter of CONVERT() function. How do you know what is this:
09/04/2009
is it April 09, 2008 or it is Sept. 04, 2008?

How you could deal with such dates?
How you could be sure which format the users use?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.