Fetching of data w.r.t the time limits in Sql Server2000

Question

0.00/5 (No votes)

See more:

hello friends.,

here I have an query which needs to pick up the complaint number which was not solved within the time frame like 6hours from the time that complaint was registered, and here i have 4coloumns saying compdate,comptime,forwardeddate,forwardedtime which are of nvarchar data type in the table urbrur means urban rural.

So for this from the net i found one query i.e,

SQL

"select compno from urbrur where forwardedtime>DATEADD(hour,6,@time)";

for that @time they used some fixed date time like

SQL

Set @Time='09/16/2011 10:00:00'

but here i can't use it because as in my table the date and time are in separate columns having datatype i.e, nvarchar and I don't want this type of fixed values, it should take the values from the date n time columns in the database . .

So friends i think i gave an complete descrption to my query so kindly do concerned to my problem and give favorable reply

I'm very thankful to u all,
praveen

Posted 21-Sep-11 0:14am

praveengb

Updated 21-Sep-11 0:26am

Toniyo Jackson

v2

Add a Solution

Comments

Om Prakash Pant 21-Sep-11 7:02am

any specific reason for creating date fields as nvarchar?
(compdate,comptime,forwardeddate,forwardedtime)

praveengb 21-Sep-11 8:23am

I'm inserting it as TextBox34.Text.ToString()in the insert query

2 solutions

Solution 2

but friend whats that @time, I don't have any column or variable declared by this name....

Posted 21-Sep-11 19:28pm

praveengb

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Om Prakash Pant · Accepted Answer · 2011-09-21T03:56:00

Solution 1

if date and time are in separate columns, then you need to concatinate the columns, convert to datetime and then use the datetime function. something like:

SQL

select compno from urbrur where 
cast(forwardeddate + ' ' + forwardedtime as datetime) > DATEADD(hour,6,@time)

(assuming that the columns will have date and time in actual formats),

Posted 21-Sep-11 3:56am

Om Prakash Pant

Comments

praveengb 22-Sep-11 1:31am

but friend whats that @time, I don't have any column or variable declared by this name....