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
MikeMike
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
>|||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
>|||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
>>
>

query

drop table share
create table share
(
shid int identity ,
dt datetime,
price int
)
insert into share(dt,price) values('1-jan-2006',10)
insert into share(dt,price) values('2-jan-2006',11)
insert into share(dt,price) values('6-jan-2006',15)
insert into share(dt,price) values('4-jan-2006',9)
select * from share
output i want is
the difference in share prices from the previous day.
so
1 (11-10)
4 (15-11)
-6 (9-15)
select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
p2.price p2price
from
(select * from share
) as p2,
share p1
where p1.shid < p2.shid
order by p1.shid, p2.shid
thisis the closest i could come up with but it has duplicates.Try,
select b.price - a.price
from t1 as a inner join t1 as b
on a.dt = (select max(c.dt) from t1 as c where c.dt < b.dt)
Why 15 - 11 and 9 - 15 instead 9 - 11 and 15 - 9?. If you meant previous
[shid] then try:
select b.price - a.price
from t1 as a inner join t1 as b
on a.shid = (select max(c.shid) from t1 as c where c.shid < b.shid)
AMB
"ichor" wrote:

> drop table share
> create table share
> (
> shid int identity ,
> dt datetime,
> price int
> )
>
> insert into share(dt,price) values('1-jan-2006',10)
> insert into share(dt,price) values('2-jan-2006',11)
> insert into share(dt,price) values('6-jan-2006',15)
> insert into share(dt,price) values('4-jan-2006',9)
> select * from share
>
> output i want is
>
> the difference in share prices from the previous day.
> so
> 1 (11-10)
> 4 (15-11)
> -6 (9-15)
>
> select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
> p2.price p2price
> from
> (select * from share
> ) as p2,
> share p1
> where p1.shid < p2.shid
> order by p1.shid, p2.shid
> thisis the closest i could come up with but it has duplicates.
>
>|||u r right. sorry for the mistake.
the query works fine.
the first query is what i was after.
i guess my approach of writing the query was wrong
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:73C20DA8-65E8-4203-BD0A-AF90394834A5@.microsoft.com...
> Try,
> select b.price - a.price
> from t1 as a inner join t1 as b
> on a.dt = (select max(c.dt) from t1 as c where c.dt < b.dt)
> Why 15 - 11 and 9 - 15 instead 9 - 11 and 15 - 9?. If you meant previous
> [shid] then try:
> select b.price - a.price
> from t1 as a inner join t1 as b
> on a.shid = (select max(c.shid) from t1 as c where c.shid < b.shid)
>
> AMB
> "ichor" wrote:
>|||I think this should work:
select shid, dt, price - isnull((Select price from share b where b.shid =
a.shid - 1), 0) from share a
Hope this help...
Ed
"ichor" wrote:

> drop table share
> create table share
> (
> shid int identity ,
> dt datetime,
> price int
> )
>
> insert into share(dt,price) values('1-jan-2006',10)
> insert into share(dt,price) values('2-jan-2006',11)
> insert into share(dt,price) values('6-jan-2006',15)
> insert into share(dt,price) values('4-jan-2006',9)
> select * from share
>
> output i want is
>
> the difference in share prices from the previous day.
> so
> 1 (11-10)
> 4 (15-11)
> -6 (9-15)
>
> select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
> p2.price p2price
> from
> (select * from share
> ) as p2,
> share p1
> where p1.shid < p2.shid
> order by p1.shid, p2.shid
> thisis the closest i could come up with but it has duplicates.
>
>|||can u explain why we need a b and c?
in the query?
"ichor" <ichor@.hotmail.com> wrote in message
news:Oe6jYSxRGHA.792@.TK2MSFTNGP10.phx.gbl...
>u r right. sorry for the mistake.
> the query works fine.
> the first query is what i was after.
> i guess my approach of writing the query was wrong
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:73C20DA8-65E8-4203-BD0A-AF90394834A5@.microsoft.com...
>|||We need to join a tabla to itself, so we need to alias the second reference
to it (b). The join condition is between a row in one side (b) and the a row
in the other side (a) where its [dt] value (in a) is equal to the previous of
current in b (the max([c.dt]) where [dt] < b.[dt]).
AMB
"ichor" wrote:

> can u explain why we need a b and c?
> in the query?
> "ichor" <ichor@.hotmail.com> wrote in message
> news:Oe6jYSxRGHA.792@.TK2MSFTNGP10.phx.gbl...
>
>|||could we have done this using only a and b and no c?
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3AD01065-EBAA-4AAE-990C-E1F29FB8BD9B@.microsoft.com...
> We need to join a tabla to itself, so we need to alias the second
> reference
> to it (b). The join condition is between a row in one side (b) and the a
> row
> in the other side (a) where its [dt] value (in a) is equal to the previous
> of
> current in b (the max([c.dt]) where [dt] < b.[dt]).
>
> AMB
> "ichor" wrote:
>

QUERY

Using sql 2000 here is my table, query, and error
Table
create table projec
(task varchar(14) not null
startdate datetime
enddate datetime)
Query
SELECT TASK "TASKS_SHORTER_THAN_ONE_MONTH
FROM PROJEC
WHERE ADD_MONTHS (STARTDATE,1) > ENDDATE
Error
Server: Msg 195, Level 15, State 10, Line
'ADD_MONTHS' is not a recognized function nameI think you want this
SELECT TASK as 'TASKS_SHORTER_THAN_ONE_MONTH'
FROM PROJECT
WHERE datediff(m,STARTDATE, ENDDATE) < 1
of if you wanted days use
WHERE datediff(dd,STARTDATE, ENDDATE) < 30
Download BOL, it will help immensely -
http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"RMSANDS" <anonymous@.discussions.microsoft.com> wrote in message
news:48E0A71C-7E83-47D6-8838-C4271F0EB6B8@.microsoft.com...
> Using sql 2000 here is my table, query, and error.
> Table:
> create table project
> (task varchar(14) not null,
> startdate datetime,
> enddate datetime);
>
> Query:
> SELECT TASK "TASKS_SHORTER_THAN_ONE_MONTH"
> FROM PROJECT
> WHERE ADD_MONTHS (STARTDATE,1) > ENDDATE;
> Error:
> Server: Msg 195, Level 15, State 10, Line 3
> 'ADD_MONTHS' is not a recognized function name.
>

QUERY

Using sql 2000 here is my table, query, and error.
Table:
create table project
(task varchar(14) not null,
startdate datetime,
enddate datetime);
Query:
SELECT TASK "TASKS_SHORTER_THAN_ONE_MONTH"
FROM PROJECT
WHERE ADD_MONTHS (STARTDATE,1) > ENDDATE;
Error:
Server: Msg 195, Level 15, State 10, Line 3
'ADD_MONTHS' is not a recognized function name.I think you want this
SELECT TASK as 'TASKS_SHORTER_THAN_ONE_MONTH'
FROM PROJECT
WHERE datediff(m,STARTDATE, ENDDATE) < 1
of if you wanted days use
WHERE datediff(dd,STARTDATE, ENDDATE) < 30
Download BOL, it will help immensely -
http://www.microsoft.com/downloads/...&DisplayLang=en
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"RMSANDS" <anonymous@.discussions.microsoft.com> wrote in message
news:48E0A71C-7E83-47D6-8838-C4271F0EB6B8@.microsoft.com...
> Using sql 2000 here is my table, query, and error.
> Table:
> create table project
> (task varchar(14) not null,
> startdate datetime,
> enddate datetime);
>
> Query:
> SELECT TASK "TASKS_SHORTER_THAN_ONE_MONTH"
> FROM PROJECT
> WHERE ADD_MONTHS (STARTDATE,1) > ENDDATE;
> Error:
> Server: Msg 195, Level 15, State 10, Line 3
> 'ADD_MONTHS' is not a recognized function name.
>