Query a Table to return Non-Date values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike Fellows - 31 Mar 2008 13:08 GMT

Hi

I have a table with dates stored in a nvarchar(75), I would like to move
this over to datetime (for obvious reasons)

the problem is the field has some 'bad data'

how can i query the nvarchar field to return me anything that isnt a date?
so i can fix and convert

Thanks in advance

Mike

Reply to this Message

Uri Dimant - 31 Mar 2008 13:20 GMT

Mike
Take a look at ISDATE () function

> Hi
>
[quoted text clipped - 9 lines]
>
> Mike

Reply to this Message

Mike Fellows - 31 Mar 2008 13:45 GMT

Uri,

I have been looking at that all morning and trying to understand it fully.

using the following query

SELECT MyDate, ISDATE(CommissionDate) AS Expr1 FROM MyTable

this returns a 1 or 0

the problem with ISDATE() is that it seems to rely on an american date
format

so 13/02/2003 is invalid whereas 02/13/2003 is valid

being in the UK the function appears to be as much use as a chocolate fire
guard :)

Thanks

Mike

> Mike
> Take a look at ISDATE () function
[quoted text clipped - 12 lines]
>>
>> Mike

Reply to this Message

Mike Fellows - 31 Mar 2008 13:51 GMT

the UK problem can be fixed by using

SET DATEFORMAT dmy;

- got it working now

Thanks again

Mike

> Uri,
>
[quoted text clipped - 34 lines]
>>>
>>> Mike

Reply to this Message