Comparing a real datetime to a constructed datetime

I have the following SQL:

select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a

where right(term,4) always returns a string which constitutes a 4 digit year eg '1999','2004',etc.

The SQL above returns

2004-04-20 00:00:00.000 Deposit ...

Which makes me think that it is able to successfully construct the datetime object inline. But then when I try and do:

select * from
(
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
) where dt >= a.submit_date

I get the following error:

Syntax error converting datetime from character string.

Given that it executes the innermost SQL just fine and seems to convert the string to a datetime object, I don't see why subsequently trying to USE that datetime object for something (in this case comparison with submit_date which is a datetime in the table a) should screw it up. Help!!! Thanks...What does:

SELECT * FROM status_view
WHERE ISNUMERIC(RIGHT(Term,4))=0

return for you?|||What does:

SELECT * FROM status_view
WHERE ISNUMERIC(RIGHT(Term,4))=0

return for you?

If I do:

SELECT top 10 right(term,4) FROM dbo.status_view

I get:

2003
2002
2003
2002
2003
2003
2001
2002
2003
2001

But if I do:

SELECT top 10 right(term,4) FROM dbo.status_view
WHERE
isnumeric(right(term,4)) =0 and

It returns no rows! I think you're on to something. But the weirdest part is if I do:

SELECT top 10 cast(right(term,4) as int) FROM dbo.status_view

I still get:

2003
2002
2003
2002
2003
2003
2001
2002
2003
2001

Ack!|||What does:

SELECT * FROM status_view
WHERE ISNUMERIC(RIGHT(Term,4))=0

return for you?

Oh wait, I was doing one and not zero. If I do:

SELECT count(1) FROM dbo.status_view
WHERE
and isnumeric(right(term,4)) = 0

it returns zero. So I guess thats not it...|||Well that just means it doesn't see anything that it doesn't think that a number...

What about...

SELECT * FROM status_view
WHERE CONVERT(int,RIGHT(Term,4))<1784|||Well that just means it doesn't see anything that it doesn't think that a number...

What about...

SELECT * FROM status_view
WHERE CONVERT(int,RIGHT(Term,4))<1784

This works fine. It returns no rows, but no errors. This is driving me insane!|||This is your problem, right?

select * from
(
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
) where dt >= a.submit_date

Which is syntactically incorrect...

what is a.submit_date? I assume it's from select *

Plus you need ro give the derived table a name

select * from
(
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
) AS XXX
where dt >= submit_date

Does that run?

comma separated values to stored procedures

Hi All,

i hv created a sp as

Create proc P @.iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR(100),iClientid) in(
@.iclientid)
end

where iclientid = int data type in the clients table.
now if i pass @.iclientid as @.iclientid = '49,12,112'

but this statement throws an conversion error ( int to char error).

is there any way to fetch records from a select statement using a
string?

Thanks in Advance.shark wrote:

Quote:

Originally Posted by

Hi All,
>
i hv created a sp as
>
>
Create proc P @.iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR(100),iClientid) in(
@.iclientid)

This does not work as you probably want. You get a selection criterium
with a single string @.iClientid. You could have written

select * from clients where CONVERT(VACHAR(100),iClientid) = @.iclientid

Quote:

Originally Posted by

end
>
where iclientid = int data type in the clients table.
now if i pass @.iclientid as @.iclientid = '49,12,112'
>
but this statement throws an conversion error ( int to char error).
>
is there any way to fetch records from a select statement using a
string?

If you need to pass in your id's the way you do, you can do it with
dynamic SQL, i.e. create a SQL statement and EXEC it.

robert|||See Erland's article on the subject:
http://www.sommarskog.se/arrays-in-sql.html
--
Hope this helps.

Dan Guzman
SQL Server MVP

"shark" <xavier.sharon@.gmail.comwrote in message
news:1152022816.209445.326230@.p79g2000cwp.googlegr oups.com...

Quote:

Originally Posted by

Hi All,
>
i hv created a sp as
>
>
Create proc P @.iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR(100),iClientid) in(
@.iclientid)
end
>
where iclientid = int data type in the clients table.
now if i pass @.iclientid as @.iclientid = '49,12,112'
>
but this statement throws an conversion error ( int to char error).
>
is there any way to fetch records from a select statement using a
string?
>
Thanks in Advance.
>

|||Where ','+@.iClientid+',' like '%,+cast(iClientid as varchar(20))+',%'

Madhivanan

Dan Guzman wrote:

Quote:

Originally Posted by

See Erland's article on the subject:
http://www.sommarskog.se/arrays-in-sql.html
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"shark" <xavier.sharon@.gmail.comwrote in message
news:1152022816.209445.326230@.p79g2000cwp.googlegr oups.com...

Quote:

Originally Posted by

Hi All,

i hv created a sp as

Create proc P @.iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR(100),iClientid) in(
@.iclientid)
end

where iclientid = int data type in the clients table.
now if i pass @.iclientid as @.iclientid = '49,12,112'

but this statement throws an conversion error ( int to char error).

is there any way to fetch records from a select statement using a
string?

Thanks in Advance.

Coming From Paradox

Does anyone know of a good tool that will convert a Paradox 11 Database to SQL 2005 with ease? Looking around and really unable to find anything...
Thanks in advanceUse DTS?

Personally, I would export out all of the data, create a staging database, assuming that you could use some enhancements in your existing model, create a solid normalized model, then write script to move the data

But that's just me