I am a Newbie to SQL and am therefore sorry if this is a stupid
question
I am trying to run the following command
SELECT OriginationNumber, DestinationNumber, StartTime, State, Duration
FROM dbo.IpPbxCDR
WHERE OriginationNumber Like 'MMColParam' or DestinationNumber Like
'MMColParam' and StartTime Between '%MMColParam2%' AND
'%MMColParam3%'
I am passing the variables MMColParam from a Web page but I always get
the following error when I try to run the command
Error converting string Datetime from Charactor String.
I have checked the DB and the value is defined as DateTime entry
here is an example of Table entry 16/11/2005 18:27:24
Can anyone help me with this?
Many Thanks
IanCrumb (rowan.ian@.gmail.com) writes:
> I am a Newbie to SQL and am therefore sorry if this is a stupid
> question
> I am trying to run the following command
> SELECT OriginationNumber, DestinationNumber, StartTime, State, Duration
> FROM dbo.IpPbxCDR
> WHERE OriginationNumber Like 'MMColParam' or DestinationNumber Like
> 'MMColParam' and StartTime Between '%MMColParam2%' AND
> '%MMColParam3%'
> I am passing the variables MMColParam from a Web page but I always get
> the following error when I try to run the command
> Error converting string Datetime from Charactor String.
> I have checked the DB and the value is defined as DateTime entry
> here is an example of Table entry 16/11/2005 18:27:24
Datetime values are not strings, but binary values. Strings can be
implicitly converted to dates, but there is no datetime interpretation
of the string '%MMColParam2%'. I don't know what you intended the
% to mean, but they don't mean anything to SQL Server in this context.
The same goes for
OriginationNumber Like 'MMColParam'
This will give a match if you have an origination number with the exact
of the strnig 'MMColParam'.
Maybe you meant something like:
SELECT OriginationNumber, DestinationNumber, StartTime, State, Duration
FROM dbo.IpPbxCDR
WHERE OriginationNumber Like @.MMColParam or
DestinationNumber Like @.MMColParam and
StartTime Between @.MMColParam2 AND @.MMColParam3
Although, you may be thinking of MMcolParam as parameters in your web
page. In such you need to pass them as parameters to SQL.
By the way, what is the intended logic? The above will not consider
StartTime if there is a match in OriginationNumber, only if there is
a match in DestinationNumber.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Many Thanks for the reply, I am one step nearer as this now runs
without errors:)
What I am trying to do is as follows:
I have a table that contains all the phone calls in and out, (Called
and Destination numbers). I am trying to create a asp page that will
search the db for a telephone number (MMColParam) in either called or
destinaition fields but only between two dates (MMColParam1 and 2)
The information you gave me helped me return a result but it returns
all calls and not just the ones between the dates specified.
Do you know what I am doing wrong in the SQL command?
Thanks Again
Ian|||Crumb (rowan.ian@.gmail.com) writes:
> Many Thanks for the reply, I am one step nearer as this now runs
> without errors:)
> What I am trying to do is as follows:
> I have a table that contains all the phone calls in and out, (Called
> and Destination numbers). I am trying to create a asp page that will
> search the db for a telephone number (MMColParam) in either called or
> destinaition fields but only between two dates (MMColParam1 and 2)
> The information you gave me helped me return a result but it returns
> all calls and not just the ones between the dates specified.
Yes, I made a remark on this flaw, but I could not be sure what you wanted.
AND binds harder than OR, so you must use parentheses:
WHERE (OriginationNumber Like @.MMColParam or
DestinationNumber Like @.MMColParam) and
StartTime Between @.MMColParam2 AND @.MMColParam3
Else you get all rows with a matching Originationumber, no matter
the start time.
Think of OR as + and AND as *.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Again,
Thanks so much for the advice but if possible I would like to pick your
brains once more.
The search still does not work, now I get nothing back because of the
date search, even if I run the date search on its own I still get no
returns
Do I need to convert the MMColParam2 and 3 to another data type?
The value of these variables is in dd/mm/yyyy format as in the data in
my DB
Many Thanks again
Ian|||Stab in the dark but, have you tried a SET DATEFORMAT DMY at the start
of your query?|||Always use Universal format yyyy-mm-dd to avoid conflict with local
settings
Also refer this
http://www.sql-server-performance.com/fk_datetime.asp
Madhivanan|||Crumb (rowan.ian@.gmail.com) writes:
> Thanks so much for the advice but if possible I would like to pick your
> brains once more.
> The search still does not work, now I get nothing back because of the
> date search, even if I run the date search on its own I still get no
> returns
> Do I need to convert the MMColParam2 and 3 to another data type?
> The value of these variables is in dd/mm/yyyy format as in the data in
> my DB
No, the data in your DB is in binary format, assuming that you store the
dates in datetime. (If you store them as strings, BETWEEN is obviously
not going to work well. Not with that format.)
Since the original snippet you posted was a bit of nonsense, it is not
clear to how exactly you get the values of these variables into the SQL
statement. In fact, I don't even know if they are T-SQL variables, or
client-side variables. Could you post that you have now, including where
you declare and set these MMColParam things?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
