|
I have 8 fields in my table:
century1,year1,month1,day1,century2,year2,month2,day2
20 01 12 01 Null Null Null Null
19 98 01 01 Null Null Null Null
I need to set century2,century2,year2,month2,day2 to the next day
after century1,year1,month1,day1, so my date will look like this:
century1,year1,month1,day1,century2,year2,month2,day2
20 01 12 01 20 01 12 02
19 98 12 31 19 99 01 01
How do I do this?
|
|
|
|
|
Why don't you use a datetime field or smalldatetime field? Then you can use the DATEADD() SQL function. to Add one day.
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
I can do this, but What should I use do get after to the 4 field again?
|
|
|
|
|
CREATE TABLE [dbo].[my_date_table] (
[date1] [smalldatetime],
[date2] [smalldatetime] NULL,
[day1] AS (datepart(day, date1),
[month1] AS (datepart(month, date1),
[year1] AS (datepart(year, date1)/100),
[century1] AS (datepart(year, date1)%100)
[day2] AS (datepart(day, date2),
[month2] AS (datepart(month, date2),
[year2] AS (datepart(year, date2)/100),
[century2] AS (datepart(year, date2)%100)) ON [PRIMARY]
The SQL Server documentation has a lot of good information about manipulating dates. You can find more information there.
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
I have a table in SQL Server 2000 as such:
categoryId
categoryParentId
categoryName
I want the categories in hierarchical structure thats why I have the parentId. All top level categories have categoryParentId equal to 0. All other subcategories have a categoryParentId that is equal to the categoryId of their parent category.
I have the following statement:
<br />
SELECT categoryId, categoryParentId, categoryName<br />
FROM categories<br />
WHERE categoryParentId=" & parentId & " OR categoryId=" & parentId<br />
The OR categoryId= will also return the parent category along with its children.
The problem that I am facing is:
I need to modify the statement in such way that it will also provide me with a TRUE or FALSE value for each category that will let me know if the category has children. I cannot change the table at all on the database. I need to do this from within the SQL statement.
Any ideas?
Thank you
theJazzyBrain
Wise is he who asks good questions, not he who gives good answers
|
|
|
|
|
I'm not more than slightly familiar with SQL Server yet, but isn't there a recordcount property you can read? Assuming that each query will return at least one result (the categoryID = parentID case), any value greater than one would indicate that the recordset contains child records.
"Your village called -
They're missing their idiot."
|
|
|
|
|
I made this and it works but I dont know if there is a better way to do it.
<br />
SELECT a.categoryId, a.categoryParentId, a.categoryName, MAX(ISNULL(b.categoryId,0)) AS categoryHasChildren<br />
FROM categories a LEFT JOIN categories b ON a.categoryId = b.categoryParentId<br />
WHERE a.categoryParentID=2 OR a.categoryID=2<br />
GROUP BY a.categoryId, a.categoryParentId, a.categoryName<br />
theJazzyBrain
Wise is he who asks good questions, not he who gives good answers
|
|
|
|
|
What about using EXISTS with a CASE statement? One of the nice things about EXISTS is that it only walks the index-- it doesn't have to retrieve a result set. In this case, it sounds like you are just wondering whether a child record exists or not.
SELECT
a.categoryId,
a.categoryParentId,
a.categoryName,
(
CASE
WHEN EXISTS(SELECT * FROM categories b WHERE b.categoryParentID=a.categoryID)
THEN 1
ELSE 0
END
) AS categoryHasChildren
FROM categories a
WHERE
a.categoryParentID=2
OR
a.categoryID=2
Especially if you are often-reading and seldom-updating the number of children, you may want to consider adding an extra column to each record containing the number of children. Then you could use triggers to update that column on the parent record when you add or remove a child.
Thank you.
Jeff Varszegi
|
|
|
|
|
This seems to be a good and fast solution.
Thanks for your feedback.
theJazzyBrain
Wise is he who asks good questions, not he who gives good answers
|
|
|
|
|
You're welcome!
Jeff Varszegi
|
|
|
|
|
I might be wrong but my SQL statement does only one SELECT but yours does two SELECT.
I have no idea how SQL server handles that, but is there a posibility that my version is faster than yours?
I am not sure about this...
theJazzyBrain
Wise is he who asks good questions, not he who gives good answers
|
|
|
|
|
Like I said, an EXISTS statement is special. It's not really a SELECT because it doesn't return a result set.
In general, you need to worry about table accesses (which may lock a table), use of indexes, doing extra work etc. when you worry about query performance. In this case, you cannot escape the fact that you have to do a subquery of some type in order to look for child records! My way actually is faster because not only does it not return a result set, but it will stop looking after the first record; your original solution (I'm not slamming it, but just explaining) looks up all the child records by joining this potentially-large table to itself and doing an expensive GROUP operation.
I wasn't sure whether or not to leave in the extra WHERE clause to retrieve the children or not, so I left it in.
I recommend pulling up the query plan for each query so you can see what's going on; this should show you a pretty good breakdown of the work done by the server. After you get done with that, you should realize that the proof is in the pudding; if you're curious about the real-world difference, do a microbenchmark!
This is as simple as making a Transact-SQL script that calls GETDATE(), loops through a certain amount of times calling a script, calls GETDATE() again, and does math to figure out the approximate time for each operation. Test a suitably large number of operations to give decent precision, and also put more than one call in each loop to minimize looping overhead. Lastly, select the results into variables, instead of returning a result set to the browser (due to inefficiencies in Query Analyzer). I usually make a stored procedure to perform a microbenchmark if I'm that curious.
Now that I think of it, one way to really speed up your performance (and provide a nice layer of abstraction from the database in the bargain) is to wrap your query in a stored procedure. This greatly reduces the parse/compile burden on the server.
It's great that you don't just take my word for it! I'm curious to hear of your results.
Regards,
Jeff Varszegi
|
|
|
|
|
Thanks for taking the time and explaining all this to me. I shall experiment next week or over the weekend at home as I will be leaving in a while. Although I have been programming in ASP for a long time, I never really thought about queries. Now we are developing large projects at work and we have to make sure that our queries are fast.
Thanks again.
theJazzyBrain
Wise is he who asks good questions, not he who gives good answers
|
|
|
|
|
I have a sql databse..
Now I'll have to write a program that will find the users for that database and each user's access access permissions.
and set permissions iff necessary!
can someone help me out in doing this!
ranjani
|
|
|
|
|
You can use the SQLDMO to view user information on a particular database. If you are using something like C# or VB .NET, you can just add a reference to your project to the SQLDMO. Then just start referencing the objects you want to use. SQLDMO.User (which can be accessed from the SQLDMO.Database.Users collection) will probably give you what you're looking for.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
I'll have to program using c++ ...using UI's but!
What is SQLDMO ?
ranjani
|
|
|
|
|
Here is a basic definition of SQLDMO that I found here: http://www.expresscomputeronline.com/20030616/techspace1.shtml
"DMO is a set of objects that represents the SQL Server infrastructure. This is a very rich and complex object model. It is not intended to provide data handling, which is best done by traditional SQL commands.
DMO is designed for administration of SQL Server using a component object model approach. In fact, using DMO, you can do things that are impossible with Enterprise Manager. Moreover, it is often much simpler to write a DMO based code rather than trying to find equivalent Transact SQL syntax for achieving certain administrative tasks."
Things I've used it for include:
- Listing available SQL Servers
- Listing available databases in a given server
- Programmatically "walking" a set of database tables for generating database objects based on those table
(just to name a few). It is a very useful component. I found a link here http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q287/7/37.ASP&NoWebContent=1 that demonstrates listing available SQL servers using C++. I'm sure there are many more examples out there, so you should just do a google search for the term "C++ SQLDMO" or something like that. There tend to be more examples out there written in VB or C#, however, the same component can be referenced from C++.
Let me know if you need any further clarification.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
I have two tables called master and child tabels.For each record in the master table i have to get the information in corresponding child table.I am using the same connection.SO, when i am trying to get information from child without closing the DbReader in Master table(I need master DbReader also.So, I am intensionally not closing it) it is giving me error that the connection is already in use.
|
|
|
|
|
In ADO.NET, you can only have one command executing against a connection at a time. The DataReader has exclusive use of a connection while it's open.
You can do one of two things: you can either open a second connection to execute the 'child' commands, or you can use a DataSet to store the results of the two commands. The first option is very wasteful of resources. The second option allows you to set up DataRelation objects which allow you to navigate the child rows using the DataRow object's GetChildRows method.
If you plan to do the second, you might as well ask for both tables in the same query batch, generating two result sets.
You could also perform a join on the server side, depending on what you actually need to do with the data.
|
|
|
|
|
Thanks. I solved the problem using the Datasets. But I have another problem . I want to read BLOB from Database. When i am using DataReader I have a function Called GetBytes which will serve my purpose. But now i am using DataSet.Can u tell me how can i Read the BLOB from a DataSet?
|
|
|
|
|
How to change the user assocuiated with dbo.
The problem is that when users enter stuff personal to them and I use the default USER (without quotes) it enters correctly DOMAIN\USERNAME, but my records insert just "dbo".
I thought that changing the owner to the admin account this would be solved.
|
|
|
|
|
When I insert a date value into Database, I can use either “Insert into TableA values(‘” + date + “’)”, or “Insert into TableA values(#” + date + “#)”.
Could you show me the difference of two SQL above?
Thanks!
xyz
|
|
|
|
|
You can't do this in SQL Server, at least with Transact-SQL. Here's a test I just ran:
CREATE TABLE #test (
dt DATETIME NULL
)
INSERT INTO #test VALUES ('2003-01-01')
INSERT INTO #test VALUES (#2003-01-01#)
On the second value, I get this error:
Server: Msg 128, Level 15, State 1, Line 1
The name '#2003' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
What language and database are you using?
Thank you.
Jeff Varszegi
|
|
|
|
|
I believe that the #01-02-2003# version is a Microsoft extension that assumes that the date uses MM-DD-YYYY format (i.e. US date format). The other format uses the default country-specific format for the computer.
In England, I commonly see PCs that are configured with control-panel defaults of "MM-DD-YYYY" or "DD-MM-YYYY" - so if you are using MS-Access and are hard-coding dates then the hash-version is probably the safer way to go. With SQL-Server, I always use the ISO "YYYY-MM-DD" format.
If you are executing the insert statement using an ADO command then I normally add parameters to the command-object because that handles casting from the original data-type, and it prevents SQL-injection attacks.
Andy
|
|
|
|
|
Hi all,
I have xml document with sql query that get results from SQL Server 2000, data returned can contain verious languages -Russian in my case- when i access the xml document directly from within IE it returns the results fine with Russian data encoded correctly.
Now i want to use ADO Stream object to access and execute the same xml document, the Russian data returned as question marks (?).
I tried to set the Charset proprty of Stream object to many values, including UTF-16, ISO-8859-1, Windows-1251 ... but nothing work, i tried also to set enocding property of the xml document to same values above, also same problem.
What should i do? also if database contains Indian or Japanese data, whats the solution?
Thanx in advance.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
