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 ... Distinct rows

I have a table as follows
ORDER_ID CODE STATUS
1000 XA3 5
1000 XA1 4
1000 XA7 5
1001 X35 5
1001 XA3 5
I want to run a query that will return the distinct ORDER_ID that is Status
= 5. If any records have Status <> 5, I dont want that ORDER_ID returned.
For example above, the result set will be 1001 only (as 1000 has one record
with Status of 4).
I have tried using 'HAVING MIN(Status) = 5 AND MAX(Status = 5) but it doesnt
appear to work :-(
Thanks in advance!
Wez
On Fri, 17 Jun 2005 03:30:02 -0700, Wez wrote:

> I have a table as follows
>ORDER_ID CODE STATUS
>1000 XA3 5
>1000 XA1 4
>1000 XA7 5
>1001 X35 5
>1001 XA3 5
>I want to run a query that will return the distinct ORDER_ID that is Status
>= 5. If any records have Status <> 5, I dont want that ORDER_ID returned.
>For example above, the result set will be 1001 only (as 1000 has one record
>with Status of 4).
>I have tried using 'HAVING MIN(Status) = 5 AND MAX(Status = 5) but it doesnt
>appear to work :-(
>Thanks in advance!
>Wez
Hi Wez,
This one should work, actually:
SELECT Order_ID
FROM YourTable
GROUP BY Order_ID
HAVING MIN(Status) = 5 AND MAX(Status) = 5
What eexactly does "doesn't appear to work" mean? Error messages? Wrong
results? Blue smoke in the server room? It's hard to help you without
knowing what's happening!
BTW, here's another query that should also work:
SELECT DISTINCT t1.Order_ID
FROM YourTable AS t1
WHERE NOT EXISTS (SELECT *
FROM YourTable AS t2
WHERE t2.Order_ID = t1.Order_ID
AND t2.Status <> 5)
/* Adding the line below might improve performance
AND t1.Status = 5
*/
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo,
when i say didnt work I mean I was getting wrong results i.e. orders
appearing in the result set that had lines not yet equal to status '5'.
However your alternative method has worked well :-)
Thanks,
Wez

query

damn touchpads......
I have a query:
SELECT id, name, rank, serial_number from status
this will return many rows...if all values are null for a single row, i need
to be able to assign a unique "id" for that row (PK constraint on another
table) is there a way to do this so that for each row that has NULL values
across all columns. This way I can populate with an incrementing or otherwis
e
unique value (using d1, d2, d3... here)
id name rank serial_number
--
d1 null null null
d2 null null null
34r bill 0-6 4420
d3 null null null
d4 null null null
d5....
TIA!You can try using the following:
NOTE: This recreates the status table (truncate + re-populate)
-- BEGIN SCRIPT
create table status
(id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
insert into status
values (null,null,null,null)
insert into status
values ('34r','bill','0-6','4420')
insert into status
values (null,null,null,null)
insert into status
values (null,null,null,null)
insert into status
values (null,null,null,null)
create table #status
(RowID int identity (1,1),
id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
create table #status2
(RowID int identity (1,1),
id varchar(10),
[name] varchar(10),
rank varchar(10),
serial_number varchar(10))
insert into #status
select * from status
insert into #status2
select * from status
update #status
set id = 'd'+convert(varchar(10), s.RowID)
from #status s, #status2 s2
where s.RowID = s2.RowID
and s.id is null
truncate table status
insert into status
SELECT id, name, rank, serial_number from #status
select * from status
drop table #status
drop table #status2
Hope it helps
"JMNUSS" wrote:

> damn touchpads......
> I have a query:
> SELECT id, name, rank, serial_number from status
> this will return many rows...if all values are null for a single row, i ne
ed
> to be able to assign a unique "id" for that row (PK constraint on another
> table) is there a way to do this so that for each row that has NULL values
> across all columns. This way I can populate with an incrementing or otherw
ise
> unique value (using d1, d2, d3... here)
> id name rank serial_number
> --
> d1 null null null
> d2 null null null
> 34r bill 0-6 4420
> d3 null null null
> d4 null null null
> d5....
> TIA!
>

query

I haven't done this for a while and i am stuck, any help would be appriciate
d.
I need to return the last employee id for each record in my database. Here
is my table.
Appt_ID, Employee_ID, Time_Stamp
1 5 7/25/05
2 7 7/26/05
2 12 7/30/05
3 2 8/2/05
I need the quert to return Appt_ID 1, 2, and 3 with the latest employee id.
Only 1 employee per Appt_ID. Any help would be great! Thanks!Try,
select Appt_ID, Employee_ID, Time_Stamp
from t1 as a
where Time_Stamp = (select max(b.Time_Stamp) from t1 as b where b.Appt_ID =
a.Appt_ID)
AMB
"B.A. Barakus" wrote:

> I haven't done this for a while and i am stuck, any help would be appricia
ted.
> I need to return the last employee id for each record in my database. Her
e
> is my table.
>
> Appt_ID, Employee_ID, Time_Stamp
> 1 5 7/25/05
> 2 7 7/26/05
> 2 12 7/30/05
> 3 2 8/2/05
>
> I need the quert to return Appt_ID 1, 2, and 3 with the latest employee id
.
> Only 1 employee per Appt_ID. Any help would be great! Thanks!|||Hi
you can do it as:
SELECT Appt_ID, MAX(Employee_ID)
FROM <EmployeeTable>
GROUP BY appt_ID
please let me know if you would like to know anything else.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"B.A. Barakus" wrote:

> I haven't done this for a while and i am stuck, any help would be appricia
ted.
> I need to return the last employee id for each record in my database. Her
e
> is my table.
>
> Appt_ID, Employee_ID, Time_Stamp
> 1 5 7/25/05
> 2 7 7/26/05
> 2 12 7/30/05
> 3 2 8/2/05
>
> I need the quert to return Appt_ID 1, 2, and 3 with the latest employee id
.
> Only 1 employee per Appt_ID. Any help would be great! Thanks!|||Apearently, On the basis of provided data Chandra's query works fine.
But I think that solution from Alejandro Mesa is the correct one
because Chandra, if we use MAX(Employee_ID), it will always means
Maximum for a perticular appt_ID, where as "last employee" should be in
respect to Max(Time_Stamp).|||thats true, but time stamp might take a new value when the data is updated
too. the new emplyee will have a max number.
that was my assumption
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Zero.NULL" wrote:

> Apearently, On the basis of provided data Chandra's query works fine.
> But I think that solution from Alejandro Mesa is the correct one
> because Chandra, if we use MAX(Employee_ID), it will always means
> Maximum for a perticular appt_ID, where as "last employee" should be in
> respect to Max(Time_Stamp).
>|||The employee could change internally to an existing employee. Think of the
situation as an account manager where accounts could be transfered between
then for various reasons. I just need a single appointment id, the id of th
e
current employee handing the account which would be based off the highest
date time stamp. Hope this helps.
"Chandra" wrote:
> thats true, but time stamp might take a new value when the data is updated
> too. the new emplyee will have a max number.
> that was my assumption
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Zero.NULL" wrote:
>|||Chandra, I understand, according to your assumption the query is
perfect. But as
B. A. Barakus has cleared his stand on this, I think he is loking for
such a query given by Alejandro Mesa.
Ok I try to clear it more, If his table is like this:
Appt_ID, Employee_ID, Time_Stamp
1 5 7/25/05
2 7 7/26/05
2 12 7/30/05
3 2 8/2/05
and if Employee_ID gets changed from 12 to any ID less then 12 (say 6)
for Appt_ID: 2 and Time_Stamp: 7/30/05
the data would look like:
Appt_ID, Employee_ID, Time_Stamp
1 5 7/25/05
2 7 7/26/05
2 6 7/30/05
3 2 8/2/05
now query by Alejandro Mesa will fetch you
1 5 7/25/05
2 6 7/30/05
3 2 8/2/05
where as query by Chandra gets:
1 5 7/25/05
2 7 7/30/05
3 2 8/2/05
Hope this solves the issue.