Thursday, March 8, 2012

Convert Sting to Date

Hi,

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

No comments:

Post a Comment