Fetching of data w.r.t the time limits in Sql Server2000
-
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, "select compno from urbrur where forwardedtime>DATEADD(hour,6,@time)"; for that @time they used some fixed date time like 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
-
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, "select compno from urbrur where forwardedtime>DATEADD(hour,6,@time)"; for that @time they used some fixed date time like 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
I'm not sure I understand the question, but perhaps you want to select rows where the comptime IS NULL and the forwardedtime is less than six hours ago (from the current time)?
praveengb wrote:
in my table the date and time are in separate columns having datatype i.e, nvarchar
That is not a good design. You should probably have just a single DateTime field for each.
-
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, "select compno from urbrur where forwardedtime>DATEADD(hour,6,@time)"; for that @time they used some fixed date time like 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
As PBC has already pointed out your database is not designed to support the query you want to run. There is no easy fix, either you will need to change the table design, or in your query you will need to concatenate the date and time values together and then convert to DATETIME datatype so that you can do the DATEADD function. That will work, but it will be quite slow if you have a large table to query so the best solution is to change the table design if you can.
-
I'm not sure I understand the question, but perhaps you want to select rows where the comptime IS NULL and the forwardedtime is less than six hours ago (from the current time)?
praveengb wrote:
in my table the date and time are in separate columns having datatype i.e, nvarchar
That is not a good design. You should probably have just a single DateTime field for each.