Adding time

Hi,
I have a table with process_id and process_started_at and
process_completed_at which are both datetime datatype.
I need to calculate the total time taken for a particular process for a
given date-range.
and calculate average time per day per process_id.
How can I do it I have MS SQL 2003.
If I add the 2 datetime variables it increments the day by 1 after
every 24 hrs but that is not what I want.
Example if I have
date1 = '2005-01-01 20:20:30'
date2 = '2005-01-01 20:25:20'
then I want the result as '40:45:50' I am not concerned with the date
part.
I tried creating a function which would add the two times but then how
do I get the average? Do I have to write another function which will
convert the total time into seconds and then devide by total number of
days and then convert back to hr:min:sec or is there is easier way to
do it?
Does MS SQL have any simple methode which will convert the time to
seconds and seconds to hr:min:sec?
Thanks for your time and expertise
Ashoo> Does MS SQL have any simple methode which will convert the time to
> seconds and seconds to hr:min:sec?
For the latter, see http://www.aspfaq.com/2271

Adding Report Filter on Float field

I am adding a filter on a float datatype field in my dataset.
Example:
=Fields!SharePrice.Value > 50
When running the report, I get the following error:
"...the processing of filter for the data set 'dataset' cannot be performed.
The comparision failed. Please check the data type returned by the data
expression."
I've tried casting the field as decimal with mixed results. Does anyone
know why this error occurs?You'd have to explicitly cast the return value using CSng() function.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uZ9L9oWWEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I am adding a filter on a float datatype field in my dataset.
> Example:
> =Fields!SharePrice.Value > 50
> When running the report, I get the following error:
> "...the processing of filter for the data set 'dataset' cannot be
performed.
> The comparision failed. Please check the data type returned by the data
> expression."
> I've tried casting the field as decimal with mixed results. Does anyone
> know why this error occurs?
>|||Try this:
Filter expression: =CDbl(Fields!SharePrice.Value)
Operator: >
Filter value: =CDbl(50)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uZ9L9oWWEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I am adding a filter on a float datatype field in my dataset.
> Example:
> =Fields!SharePrice.Value > 50
> When running the report, I get the following error:
> "...the processing of filter for the data set 'dataset' cannot be
performed.
> The comparision failed. Please check the data type returned by the data
> expression."
> I've tried casting the field as decimal with mixed results. Does anyone
> know why this error occurs?
>|||Thanks. That did the trick.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eNogP1WWEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Try this:
> Filter expression: =CDbl(Fields!SharePrice.Value)
> Operator: >
> Filter value: =CDbl(50)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
> news:uZ9L9oWWEHA.3716@.TK2MSFTNGP11.phx.gbl...
> > I am adding a filter on a float datatype field in my dataset.
> >
> > Example:
> >
> > =Fields!SharePrice.Value > 50
> >
> > When running the report, I get the following error:
> >
> > "...the processing of filter for the data set 'dataset' cannot be
> performed.
> > The comparision failed. Please check the data type returned by the data
> > expression."
> >
> > I've tried casting the field as decimal with mixed results. Does anyone
> > know why this error occurs?
> >
> >
>

Adding datetime to database?

I have DateCreated with datetime datatype in my SQL Express 2005. I'd like to add the record to my Task table so I have a form in my ASPX and create a button event in my ASPX.CS here is the code

protected void Button_AddTask_Click(object sender, EventArgs e)
{
SqlDataSource newTask = new SqlDataSource();
newTask.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
newTask.InsertCommand = "INSERT INTO [Task] ([MemberAccountName], [Title], [Place], [TaskDetail],[DateCreated]) VALUES (@.MemberAccountName, @.Title, @.Place, @.TaskDetail,@.DateCreated)";
newTask.InsertParameters.Add("MemberAccountName", User.Identity.Name);
newTask.InsertParameters.Add("Title", TextBox_Title.Text);
newTask.InsertParameters.Add("Place", TextBox_Place.Text);
newTask.InsertParameters.Add("TaskDetail", TextBox_Detail.Text);
newTask.InsertParameters.Add("DateCreated",DateTime.Now.ToString());
newTask.Insert();
Response.Redirect("Default.aspx");
}

but I got this error

Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

Any idea?
Sorry your guy, I think I found the problem...

In my SQL Express 2005 use the datetime format in DD/MM/YYYY

butDateTime.Now.ToString() giveMM/DD/YYYY

I found it by hardcoding "22/12/2005 00:00:00" and it works!!

So now how can I fix this??
|||

Check the other ways to use the add, there should be one that allows you to specify the parameter type. Tell it that it is a datetime, and pass in datetime.now (NOT datetime.now.tostring).

|||You can avoid date problems by always presenting your date in YYYYMMDD format to SQL Server.

In your original code, try this instead (add a format to the ToString method):

newTask.InsertParameters.Add("DateCreated",DateTime.Now.ToString("yyyyMMdd"));

|||Thank you everyone, now I can make it work

However there is a little problem with my Thai Buddhist year !!

There is 543 year different between Christ year and Buddhist year.

I can get the Datetime.Now show correctly in the page but when inserting into the database the system will add 543 automatically to the year !!! So when I retrieve the data back from the database it get the year 3091 !!

So what I have to do is
.
DateTime dt = DateTime.Now;
.
.
newTask.InsertParameters.Add("DateCreated", dt.AddYears(-543).ToString("yyyyMMdd HH:mm:ss"));
.

It's kind of weird, right??|||See my post 3 posts up.