|
I'm guessing that the difference is probably because one of the conversions, the .NET one, rounds or truncates the time to hundredths of seconds. Try the comparison with the time as .120 in each case then using .125 in each case. The first will likely give the same result and the second might give a difference of 5 milliseconds.
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
If _date is '2006-11-13 14:57:02.343' in .NET. DateTime.FromOADate(_date.ToOADate()) returns '2006-11-13 14:57:02.343' as well. Which meens that either there is no rounding or it's too late and I didn't undertand you. In any case I work with timestamps and don't have the luxery to choose milliseconds. I can solve it by choosing another pk than a datetime column. But I found it very strange that SQL and .NET using different algoritms.
Tanks,
|
|
|
|
|
SQL's DateTime Data type is only accurate to 3.33 milliseconds, and always rounds like this .000,.003,.007). .Net's DateTime is accurate to .0000001 sec (100 nanoSec.). Both are documented (see SQL books online for SQL types, MSDN for .Net. SQL has been around for 10+ years, and is unchanged for compatibility with older versions. .Net is new, and takes advantage of the higher accuracy available on modern PCs (Not all can deliver that accuracy). If you need maximum resolution, make yor database field a bigint type (long) and store DateTime.Ticks there. Handle the conversion yourself in .Net.
We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene
|
|
|
|
|
That I know. If I would create a double in .NET code using ToOADate() and then save it in the db as DateTime that suddenly would be a problem. Example: '2006-11-30 12:00:00.0001' in .NET would become '2006-11-30 12:00:00.000' in sql as you pointed out. But, the date is created in the db. So it's db's resolution. If you take an exectly same date down to milliseconds it should return a same value.
|
|
|
|
|
|
I Have used full text indexin my project.
I want to find "Full Text Index Catalog Creation" Time duration.
Is there any method to find.
I am using SQL Server 2005
Regards
Vasanth
|
|
|
|
|
I am new to SQL Server. I want learn basics abt
1. Stored Procedures
2. View
3. Index
4. Triggers
5. Function
6. Cursors
7. Locks
Can anyone help
Mads
|
|
|
|
|
Try W3Schhols !!! or buy a book. Microsoft offer free online courses sometimes, Have you tried book on line for SQL server ?
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
There are numerous books that you may want to take a look at. You may want to take a look at Microsoft SQL Server 2005 for Dummies, or Sams Teach Yourself SQL Server 2005 Express in 24 Hours.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I have a table which has
Class Name
1A Albert
1A Bill
1A Paul
1B Ricky
1B Kelvin
1B Walala
I want to convert it to a table which is
Class FULL LIST
1A Albert/Bill/Paul
1B Ricky/Kelvin/Walala
I dont know how to write the query. Pls kindly help
|
|
|
|
|
Which database are you trying to run this in. If it's SQL Server 2005, you can use a thing called a Common Table Expression to help you out with this. If it's not, (and I apologise to the Oracle guys if they have something whizzbang like CTE's), you will need to write a recursive query. These aren't fun, but examples can be found on Google.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Oh no.....I am SQL Server 2000....But anyway, thank for helping.
|
|
|
|
|
You could do something like this:
CREATE FUNCTION GetNames (@cClass varchar(2))
RETURNS varchar(1000) AS
BEGIN
DECLARE @cClassNames varchar(1000)
DECLARE @cClassName varchar(50)
SELECT @cClassNames = ''
DECLARE namecursor CURSOR FOR
SELECT DISTINCT Name
FROM classtable
WHERE Class = @cClass
OPEN namecursor
FETCH NEXT FROM namecursor INTO @cClassName
WHILE @@FETCH_STATUS = 0
BEGIN
select @cClassNames = @cClassNames + @cClassName +'/'
FETCH NEXT FROM namecursor INTO @cClassName
END
CLOSE namecursor
DEALLOCATE namecursor
RETURN(@cClassNames)
END
Call it like this:
select class, dbo.GetNames(class) as [Full List] from (select distinct class from classtable) as tbl
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Nice - I was working on one which didn't use cursors (it used stored functions), but there's no need now as yours does the job perfectly.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Many Many Thanks. You give me a great great hand.
|
|
|
|
|
We are trying to develop a windows application in c#.
There are many classes which should have access to database(SQL Server). I do not want each class to write their queries.However, I want to give them an interface or another way that they can acess database easily. For example, they should be able to say "this.insert(), this.delete()" or something like this. I have checked Facade pattern. Is there another way of doing this.I want to have some other options.
thanks...
aysan ethem narman
|
|
|
|
|
Write a dal class (or classes). These control access to the database. I tend to write mine as a C# Façade on to the stored procedures. In other words, for each method on the DAL is one stored procedure.
|
|
|
|
|
hi i m working on already devloped project .already existing view is there. i want to the view is created from which table .
help me
|
|
|
|
|
If you double-click on the view in Enterprise Manager (or right-click and select "Properties") you can see the SQL that was used to build the view.
You can do this as a query also:
select [text] from sysobjects
inner join syscomments on sysobjects.id = syscomments.id
WHERE sysobjects.name = <<view name here>> AND xtype = 'V'
|
|
|
|
|
I have list of users in the database, in the form once the users are selected, selected users should not display again while again selecting users. so I want to use NOT IN query. How can i use it.
|
|
|
|
|
swguy23 wrote: so I want to use NOT IN query. How can i use it.
SELECT *
FROM MyTable
WHERE id NOT IN(1, 3, 5, 7, 13, 17)
|
|
|
|
|
What is FillFactor?
How it it will help to increase the Performance?
Regards
Vasanth
|
|
|
|
|
Fill factor is the initial percentage target for storing data on a page. It is used to prevent page splitting when a row with a new clustered index value is added that would fall between existing values - if the page has room, the value can be inserted without allocating a new page, and requiring data to move at most within the current page. If the page is full when this happens, a new page must be allocated, data moved to it to make room for the new row, then the new row inserted. Generally it is best to leave this at the default, unless you have clear evidence that frequent page splitting is a problem. Lower fill factors reduce the incedence of page splitting, but at the expense of potenially more wasted (allocated but unused) space. Fine tuning this can sometimes improve insert performance. it has no impact on read, update or delete performance (except that very low fill factors can hurt reads by forcing more seeks).
We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene
|
|
|
|
|
I have written an update trigger. It is given below:
CREATE TRIGGER Trg_OnUpdateJSComman ON
[dbo].[JSComman]
FOR UPDATE
AS
begin
declare
@uid bigint
update JSCvs set LastUpdated=getdate()
where
(UID=@uid)
end
if any updates happen in the JSComman table this trigger will have to execute. but i need to pass the uid into the trigger for this to execute. is it possible to pass parameters into the trigger?
Thanks in advance.
Lavanya
|
|
|
|
|
You shouldn't need to pass a parameter into a trigger. As a simplification, it has access to the INSERTED and DELETED tables, so you could write the above trigger as:
UPDATE JSCvs SET LastUpdated = GETDATE()<br />
WHERE UID = (SELECT UID FROM INSERTED)
Whenever you do an update, the tables INSERTED and DELETED store copies of the modification. Deleted stores the old version, and Inserted stores the updated version.
I hope that helps.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|