|
| PORTAIL | ANNUAIRE | ARTICLES | COMPARATEUR HÉBERGEURS | DEVIS | FORUMS | RÉDUCTEUR D'URL |
|
|
|
|
||||||
 |
|
|
LinkBack | Outils de la discussion |
31/03/2008, 13h08
|
#1 |
Messages: n/a
Hébergeur: |
Query a Table to return Non-Date values
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 |
|
|
31/03/2008, 13h20
|
#2 |
|
Messages: n/a
Hébergeur: |
Re: Query a Table to return Non-Date values
Mike
Take a look at ISDATE () function "Mike Fellows" <mike.fellows@equityhouse.NO.SPAM.co.uk> wrote in message news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@giganews.com ... > 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 > |
|
|
|
31/03/2008, 13h45
|
#3 |
|
Messages: n/a
Hébergeur: |
Re: Query a Table to return Non-Date values
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 "Uri Dimant" <urid@iscar.co.il> wrote in message news:OTLvllykIHA.5660@TK2MSFTNGP02.phx.gbl... > Mike > Take a look at ISDATE () function > > > > "Mike Fellows" <mike.fellows@equityhouse.NO.SPAM.co.uk> wrote in message > news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@giganews.com ... >> 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 >> > > |
|
|
|
31/03/2008, 13h51
|
#4 |
|
Messages: n/a
Hébergeur: |
Re: Query a Table to return Non-Date values
the UK problem can be fixed by using
SET DATEFORMAT dmy; - got it working now Thanks again Mike "Mike Fellows" <mike.fellows@equityhouse.NO.SPAM.co.uk> wrote in message news:UpudnVw4esuFQG3anZ2dnUVZ8v6dnZ2d@giganews.com ... > 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 > > > > > "Uri Dimant" <urid@iscar.co.il> wrote in message > news:OTLvllykIHA.5660@TK2MSFTNGP02.phx.gbl... >> Mike >> Take a look at ISDATE () function >> >> >> >> "Mike Fellows" <mike.fellows@equityhouse.NO.SPAM.co.uk> wrote in message >> news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@giganews.com ... >>> 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 >>> >> >> > > |
|
|
|
| Outils de la discussion | |
|
|
