Time comparison w.r.t the system time

Question

0.00/5 (No votes)

See more:

hello friends,

I need an one query that gives particular complaint number which has some fixed time duration like 6hrs within that it has to solved other wise that should be displayed as output..

Now my problem is i'm adding that 6hrs to the time at which complaint has registered and comparing it with the system time.. here's my query..

C#

"select compno,compdate,comptime from urbrur where status='Under Process' and DATEADD(hh,6,comptime)<=GETDATE()"

but the output 'm getting is inconsistent . .

Compno Compdate comptime
16388 8/31/2011 10:11 AM
16790 9/15/2011 8:29 PM
16905 9/22/2011 5:05 PM
16906 9/22/2011 5:37 PM
17068 9/30/2011 5:24 PM
17085 10/1/2011 6:43 PM
17086 10/1/2011 6:48 PM
17087 10/1/2011 6:53 PM

this is my smaple data fetching it at the system time of 7:17 PM on october 1st.

so w.r.t this query and with this system time as i think the last 3complaints should not be displayed i'e, for 17085 6:43pm + 6hrs means 12:43am is not <= current system time 7:17 PM then that complaint nomber shouldn't be displayed..then kindly plz suggest me friends, i'm very much needed from last 10days . .

friends other thing is to say that the

compno-numeric;compdate-nvarchar(50);comptime-nvarchar(50);

And i'm using SqlServer2000,VS2008, ASP.Net with C#

Thanking You,
praveen

Posted 1-Oct-11 4:58am

praveengb

Updated 1-Oct-11 5:01am

Mehdi Gholam

v2

Add a Solution

3 solutions

Solution 3

Sounds like you are working on a workflow application, so I thought you might find Workflow foundation to be of some interest:
Jump start Windows Workflow Foundation[^]

There are some tutorials available on MSDN[^] too.

Best regards
Espen Harlinn

Posted 1-Oct-11 6:12am

Espen Harlinn

Comments

Simon Bang Terkildsen 1-Oct-11 12:35pm

I've still not had the time to look into WF, but I didn't get the sense that it was worth much, when I quite awhile back took a peek at it.
My 5 though :)

Espen Harlinn 1-Oct-11 12:50pm

It takes a bit of work to make it really shine, there are many integration points worth investigating. The default sql server database works well enough for small to medium stuff - it's when you customize the storage and adapt it to your own desings that things get really interesting :)

Amir Mahfoozi 3-Nov-11 7:36am

+5 thanks for the link

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mehdi Gholam · Accepted Answer · 2011-10-01T05:15:00

Solution 2

It is better to store the date and time together as a datetime column as well, then your query will run correctly on that column.

SQL

select compno,compdate,comptime from urbrur where status='Under Process' and DATEADD(hh,6,CAST(compdate + ' ' + comptime as datetime)<=GETDATE()

Posted 1-Oct-11 5:15am

Mehdi Gholam

Comments

Simon Bang Terkildsen 1-Oct-11 11:19am

Well great minds think alike, as there are no saying that says "stupid minds think alike" I deduce that we're great minds :D

Mehdi Gholam 1-Oct-11 11:25am

hehehehe

Espen Harlinn 1-Oct-11 12:09pm

5'ed!

praveengb 4-Oct-11 7:25am

thank you sir.. I got the result with this query . . but sir, I'm unable to display it in the grid view i.e,

protected void Menu1_MenuItemClick(object sender, MenuEventArgs e)
{
if (Page.IsPostBack)
{

if (Menu1.SelectedItem.Text == "A")
{

string _query2 = " select compno,name,address,phoneno,complaint,compdate,comptime,contactno,status from urbrur where cat1='" + Menu1.SelectedItem.Text + "' and status='Under Process' and DATEADD(hh,6,cast(compdate + ' ' + comptime as datetime))<=GETDATE()";
comd = new SqlCommand(_query2, cn);
sqlda = new SqlDataAdapter(comd);
ds = new DataSet();
sqlda.Fill(ds, "urbrur");
GridView1.DataSource = ds;
GridView1.DataMember = "urbrur";
GridView1.DataBind();
cn.Close();

}

}
}
When i click on the that menu button . . the plane grid is displaying without any data..here I'm not using default grid columns.. in the grid i created 9 columns as BoundField with one checkbox field total 10 columns.. so plz suggest me what to do..

Simon Bang Terkildsen · Accepted Answer · 2011-10-01T05:08:00

Solution 1

You state that 12:43AM > 7:17 PM but that's not true.

You'll have to use the date as well.

SQL

select compno,compdate,comptime
from urbrur
where status='Under Process' and
      DATEADD(hh,6,compdate + ' ' + comptime) <= GETDATE()

Posted 1-Oct-11 5:08am

Simon Bang Terkildsen

Comments

Mehdi Gholam 1-Oct-11 11:16am

posted the same, my 5

praveengb 12-Oct-11 2:36am

hello Sir,

I want to store the Attended date n time in the database.. I have the 2 separate columns for date n time as nvarchar in the table urbrur.

I have a one gridview in that i have 2columns and one checkbox one is for compno n other for status where the value of status is either Attended or Under Process..

now i'm displaying only under process complaints n on checking of the respective check box the status of the compno is updated as Attended in the database table called urbrur..but now i want to store the current date n time as well in the urbrur as at what date n time that respective copmno is get Attended . .

So how should i display the current date n time in the existing gridview and on checking of that particular check box that date n time also should be get stored in the table urbrur[here i have 2separate columns for date and time as nvarchar]

So kindly plz suggest me to how i do make it . .'m very much indeed needed.

Thx,
praveen

Espen Harlinn 1-Oct-11 12:09pm

5'ed!

Simon Bang Terkildsen 1-Oct-11 12:30pm

Thank you, Espen

Time comparison w.r.t the system time

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

Time comparison w.r.t the system time

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us