|
if you save dates in the integer format like this: YYYYMMDD then you should be able to do something like this to get a count of subscribers on April 20, 2006:
SELECT COUNT(*) FROM Subscribe_Table WHERE Date_Column = 20060420
To get a count of subscribers since May 12, 2006:
SELECT COUNT(*) FROM Subscribe_Table WHERE Date_Column > 20060512
To get a count of subscribers between April 14 and May 21 of 2006:
SELECT COUNT(*) FROM Subscribe_Table WHERE Date_Column > 20060414 AND Date_Column < 20060521
|
|
|
|
|
Uhm... the value is a DateTime. What can i do without change the database?
|
|
|
|
|
|
Okay, I'm assuming you are using SQL Server, and the column is a DATETIME or SMALLDATETIME.
For a single day you can use this:
SELECT * FROM MyTable WHERE [date] >= '2006-04-20 00:00:00' AND [date] <= '2006-04-20 23:59:59' If you are only storing the date element without the time then you can do a simpler [date] = '2006-04-20' instead.
The same works for longer date ranges. e.g.
SELECT * FROM MyTable WHERE [date] >= '2006-04-14 00:00:00' AND [date] <= '2006-05-21 23:59:59'
Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Does this work?
on April 20th 2006
SELECT COUNT(*) AS totalsubscribers FROM subscribers WHERE CONVERT(VARCHAR, datesubscribed,101) = '04/20/2006'
since May 12th 2006
SELECT COUNT(*) AS totalsubscribers FROM subscribers WHERE CONVERT(VARCHAR, datesubscribed,101) >= '05/12/2006'
between April 14th and May 21st
SELECT COUNT(*) AS totalsubscribers FROM subscribers WHERE CONVERT(VARCHAR, datesubscribed,101) BETWEEN '04/14/2006' AND '05/21/2006'
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
That would do a string based comparison, so you would be better using an ISO format where it puts YYYY-DD-MM (i.e. most significant digits first) - It also keeps it culture neutral.
You don't need to convert the DATETIME or SMALLDATETIME into varchars either because a string literal will be implicity converted to a date if the format is appropriate.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Oh. I figured that the conversion would be necessary in order to get rid of the time portion from the datesubscribed field. In your example you provided the time: '2006-04-20 23:59:59' - but it seemed like he just wanted to compare dates.
How would you do this?
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
If the [date] column in the database contains no time elements then, say, 22-May-2006 would be stored as midnight (00:00:00). In that case you wouldn't need to do the 23:59:59 (or 23:59 on a SMALLDATETIME) thing.
It has confused me why there isn't a useful built-in date only function (to extract just the date part) for comparisons such as this.
Solutions I've used in the past include:
-- Performance is okay
...WHERE DAY([date]) = 22 AND MONTH([date]) = 5 AND YEAR([date]) = 2006
-- Performance is abysmal on large datasets and useless if part of a join (What was I thinking!?!)
WHERE [date] = dbo.datetime_rounddown(@someDateTime)
-- Okay as a replacement for a column that is being SELECTed
SELECT dbo.datetime_rounddown([date]) FROM MyTable
-- If using a temp table to add an extra column that specifically excluded the time portion
-- Or if the table is sufficiently large, you could add an extra column to hold the date only version
I've never tried to convert it to an varchar and use that so I don't know what the performance is like, but it would have to be the ISO format because greater/less-than comparisons wouldn't work properly, e.g. (using the British format)
22/05/2006
22/05/2007 -- Jump a year
23/05/2005 -- then jump almost two years back again.
You'd get a similar situation with the US format date too.
If the comparison is something that is going to be done a lot and the table is large, then it may be worth considering denormalising the data model slightly to boost the performance. I've done that before where the table was holding millions of rows. Some queries went from taking several minutes to taking a few seconds.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
I get this error...
You are not authorized to view this page - HTTP Error 403 - Forbidden
Win XP SP 2, SQL 2000 Reporting Services SP1
I can develop and preview a report in VS 2003 but cannot run or deploy it. Nor can I browse to the ReportServer in IIS. Tried googling on web and newsgroups but no joy so far.
At home with VS 2005, SQL 2005 Reporting Services everything works.
Kevin
|
|
|
|
|
Are your permissions and security set correctly where the page is located?
|
|
|
|
|
Well, after quite a bit of fiddling around, running reporting services utilities, etc., I can now run, execute and deploy without errors. However, I cannot actually get the report to display in the browser. For example, I just get a directory listing of the ReportServer folder. If I browse to the Reports folder I can see that my report is there in the folder I designated. But clicking on the .rdl file just takes me to a display of the ReportServer folder!
Kevin
|
|
|
|
|
|
I am having some ids as a collection of strings(more than one)
For each id I want to select some columns from joining three tables
For this the above id maps with two tables
The snippet of query is
SELECT InventoryID,InventorySubTypeID,Make,Model,SerialNumber,OriginalCost,NetValue,InventoryStatusID, isnull(null ,0) [Adjustment Amount],isnull(null ,0) [New Net Value] ,null [Change Status To],null [Comments] from Inventory_Profile where InventoryID in (inventoryIds)
can u guide me with source
thanx
-
-- modified at 9:08 Monday 22nd May, 2006
|
|
|
|
|
|
Hi all ,
I had developed a project named as Online Examination in Visual Studio 2005 Beta version. Backend for my project is Sqlserver 2000.Which is web based application. So i created a connection object and stored in the Application level.I conducted the exam in a server which have the 100 clients , the server is high end server (IBM server).My examination is divided into number of slots, per day 5 slots.At the first day , after two slots , while in third slot my application had blocked , no button was working. After a R&D i found that a deadlock is occuring in MS Sql server. Can any one tell me the actual problem for this and how to avoid this situation.I am new to Dotnet, this is my first project.
Regards
Srinivasa Rao
|
|
|
|
|
srinuivasaRaoS wrote: Can any one tell me the actual problem for this
No, because you have not provided enough information. (e.g. What code was running on the SQL Server at the time of the deadlock)
srinuivasaRaoS wrote: how to avoid this situation
Typically deadlocks occur because two or more things request the same resource while holding on to other resources that the other wants. For example
Process 1 Process 2
Requests Lock on Table A Requests Lock on Table B
Gets Lock on Table A Gets Lock on Table B
Requests Lock on Table B Requests Lock on Table A
Blocks until Table B released Blocks until Table A is released
Deadlock detection kicks in and one of the processed become the deadlock victim
Deadlock victim:
Lock released on Table B and Transaction Rollsback
Gets Lock on Table B
Completes request.
The solution is to try and ensure that you aquire resources in the same order in each query (easier said than done). So, you can always detect the deadlock in your exception handler (catch block) and re-attempt the query. The SqlException will return a Number of 1205 for a deadlock.
As you are using VS2005 you will have access to the new asyncronous access to the database. You could use this to ensure the application is still responsive while it waits for an answer from the database.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Hi Colin Angus Mackay,
Thanks For replying to me,Actually we conducted an online examination which developed in VS2005 Beta version, backend is Sqlserver-2000.I have one server and around 100 clients are connected to the server.My database is also presented in the same server.I am updating the one table for all the clients, which is a status table.And one table for login details.I think this information is enough , if u need still more information , u tell on wht u need , i will tell u the information immedaiately,
Thanks & Regards
Srinivasa Rao
|
|
|
|
|
Then what is happening is that in one transaction you are using the status table and the login table and in some other transaction that is running at the same time you are using the login table and the status table.
I suggest that when you need to use both tables in a single transaction that you use ether the status table and the login table always, or use the login table and the status table always.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Thank You colin Angus Mackay,
I have one more doubt , instead of using database table , shell i use xml files for updating the each user status, in the sense one xml file will be created per one user.After that i will write another code which will update the status of each user after completion of exam, is it right way ?.If i will do in this way whether i face any problem.Please Let me know
Thanks & Regards
Srinivasa Rao S
|
|
|
|
|
srinuivasaRaoS wrote: shell i use xml files for updating the each user status, in the sense one xml file will be created per one user.After that i will write another code which will update the status of each user after completion of exam, is it right way ?.If i will do in this way whether i face any problem.Please Let me know
You will just replace row, page and table locking issues for file locking issues. You would be better to figure out what is causing the deadlocks and fix that.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
My sql storedprocedure contain one SELECT * statement and one RETURN VALUE
Now i want bind the result set of the select statement to datagrid control and return value to the text property of the label control.
Is it possible to bind the data like this?
If possible... HOW??
|
|
|
|
|
Read my article on just such a subject
You need to specific the a SQLparameter as out on your ASP.NET and return records to dataset / datareader.
This is certainly possible.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
Hi,
If i am writing simple select query like
Declare @parentInventoryId int
SELECT InventoryID
FROM Inventory_Profile
WHERE
ParentInventoryID=@parentInventoryId
In query execution plan i am getting index scan, but i need index seek
can anybody tell how to change the query to get index seek in my query execution plan
|
|
|
|
|
On what column do you get an indexscan ?
did you make indexes ? on what columns ?
did you make a covering index ?
by adding the requested data and the filtering data in one index, sql does not have to go to the table itself, just the index is enough
how many rows of data are there in this table ?
SQL server ignores indexes when there are few rows of data
My second computer is your linux box.
|
|
|
|
|
I am currently looking for links to papers (articles) on Microsoft Access team development for a research paper. If anyone can direct me to good links or even give some search parameter strings, I would appreciate it. Mean while I will be continuing my search.
Thanks,
INTP
"Program testing can be used to show the presence of bugs, but never to show their absence."Edsger Dijkstra
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
