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
IanSee if these help:
http://www.karaszi.com/SQLServer/info_datetime.asp
Guide to Datetimes
http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm
Datetime Searching
--
Andrew J. Kelly SQL MVP
"Crumb" <rowan.ian@.gmail.com> wrote in message
news:1141251396.268280.282650@.p10g2000cwp.googlegroups.com...
> 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
>
> Ian
>

Convert numbers to Month Name

I have a stupid field in my database that doesn't hold a date but I have to use it to determine the month in my Chart in SSRS 2005. So for example, check this out:

http://www.webfound.net/chart_months.jpg

Ok, so how can I change those numbers to the Month Name? I tried MonthName() around my field in the expression builder, but that's only for a datetime field.

Write a UDF with input as int month and return it as varchar month. Use 13 CASE statements (12 for 12 months and 1 for error)|||

How about something like convert(datetime,'2006' + <fieldname> + '01') within your SQL statement?

for padding 0's

select convert(datetime,'2006' + fieldname + '01')
from
( select case <fieldname> when > 9 then <fieldname> else '0' + convert(char(1),<fieldname>) end fieldname ) derivedtable1

|||

The function MonthName, in SRSS2005, accepts an Integer parameter, so I'm not sure I understood the question.

|||hmm, then I wonder why that didn't work...MonthName(fieldname)|||

Paulo X , look at the link to my chart above. Those are integer values coming in from my dataset on a field in our DB table called systemmonth. Don't ask me why they did it that way but I need to take those values and convert them to Month Names....hopeing to do this either through SQL or preferably using a function in Reporting Server 2005 as you stated. I tried wrapping the systemmonth like this but it didn't have any affect:

MonthName(myfieldname)

|||

this is what I'm talking about, I put this in as a category group field and created this expression behind it:

=MonthName(Fields!SystemMonth.Value)

|||

What happens when you use MonthName? Is there any error message?
You say that you have used the MonthName function into the group expression. You must used it also in the Label expression! In fact, you may use MonthName only in the Label, you don't need to use it to group data.

Regards

|||

Hi,

Just try

=Monthname(1) or

=MonthName("1")

Is it working. then assign your field values. the second statement also works.

Amarnath