|
Mycroft Holmes wrote: do the datediff 3 times getting the Y/M/D separately and concatenate them as you want.
That won't work. Consider:
declare @start date = '20121231';
declare @end date = '20130101';
select
cast(datediff(year, @start, @end) as varchar(5)) + ' year, '
+ cast(datediff(month, @start, @end) as varchar(5)) + ' month, '
+ cast(datediff(day, @start, @end) as varchar(5)) + ' day'
;
The output will be 1 year, 1 month, 1 day, which is obviously wrong.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
As stated, the query you showed will provide the wrong results, however, that doesn't mean datediff 3 times can't be used. There is just more to it.
Tim
|
|
|
|
|
You are right, a simple datediff 3 times will not do it, however with the application of a little imagination and some work with SSMS I know you can do this, I have seen it done in an example somewhere but dammed if I am willing to chase it down.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
As other's have said, formatting shouldn't be done in SQL. However, if that is the requirment of the application, other replies do provide some insight.
Post what you have and then it can be reviewed and commented on.
Tim
|
|
|
|
|
I am in the middle of development on a WPF/SQL 2008 project.
As I make changes to the application I need to push changes to my client for testing. So I can create a ClickOnce for the binaries, but what's the right way to push DB changes?
Thanks
If it's not broken, fix it until it is
|
|
|
|
|
We use Red-Gates SQL Compare to deploy to UAT (production deployment is a more stringent process). Data compare to synch the master tables. I think it can be automated but we do a manual deployment as reports are built against UAT and not the dev database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm experimenting with encrypting some values in SQL Server (2012). This requires that I open the appropriate key(s) for the user/role. I haven't seen any information on doing this automatically.
I have written a procedure that will open keys by querying a table that associates users with keys. When I open the database in ADO.net via C#, I can simply check for the existence of the procedure and execute it before proceding.
[I see that procedures can be executed when the server starts, but that's not what I want.]
But it seems like there should be a standard/conventional/"best practice" way to do this at the database level so that the keys get opened even when I connect from SSMS or SSIS.
Can keys be opened automatically depending on the user?
Can a procedure be executed automatically when a user connects?
|
|
|
|
|
More of a poll than a real question, I guess, but here goes:
I have a database that contains information on how to, and how much it costs, to repair something. The cost is derived by adding the cost of all materials and all labor associated with a repair. The "defect" is stored in one table, and it's associated repair steps, materials, etc... are stored in other tables in a one to many relationship.
Long story short, every time I retrieve the cost of one of those defects, which is quite frequent, I have a UDF that figures the cost. I was thinking about storing that derived total cost as a field instead of processing the cost each time, as it would only be calculated each time the defect is changed or saved, vs. every single time someone requests the defect. I am generally a data purist and the thought of storing data I can derive chafes me, but it does seem like a lot of over processing.
What are your thoughts? Right now the table is only about 1500 records and it is unlikely to grow larger than that.
|
|
|
|
|
If the system performance is currently acceptable and is unlikely to change based on data growth, they why change the system?
However, if you view this as an opportunity to improve the system design and it will not adversely affect other work or performance, then change it.
|
|
|
|
|
|
Personally I'm with Tim, I consider your design the technically correct one. Technically correct is not always the best design, it is the one I prefer but if I thought storing the data would give me a better solution I'd have no compunction in changing it. Only you can asses the benefit to changing but I would think 1500 is a trivial number to deal with, add a few zeros and I'd look at it again!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Would you expect any speed different if I doing this with same computer hardware like I7,16GB ram and
case 1: ubuntu 64bits + java + mysql
case 2: windows7 64bits + c# + microsoft sql server 2012
modified 26-Jun-13 14:32pm.
|
|
|
|
|
Okay, what's going on here? I swear I've seen this question in here way too frequently. Let me guess, it's for a trading system?
Is this an exam question? An interview question? Is someone advertising contract work and asking this?
|
|
|
|
|
This is a more detail programming question already, not as general as before.
|
|
|
|
|
In that case, I would post in QA or other proper forum; programming questions are not allowed in the Lounge.
|
|
|
|
|
Above says "Technical discussions are welcome"
|
|
|
|
|
|
You are damn right. Smells toasty in here - how Long will he survive? 
Edit - Before this gets more reports - I posted this right after the thread was moved over here from the Lounge - The Link was still at the start page.
modified 27-Jun-13 3:23am.
|
|
|
|
|
Now that it's been moved from the Lounge to the Database forum should buy him a few extra minutes.
BDF
The internet makes dumb people dumber and clever people cleverer.
-- PaulowniaK
|
|
|
|
|
Seems like we made someone angry - We both got a downvote on our posts 
|
|
|
|
|
To answer your question, yes I would expect a difference.
- Different OS means potentially different thread scheduling, which may result in a different execution order and different in context switching overheads. There's a time difference.
- Different programming language on different VMs, means different compilers, different executable code (and in this case, each executes on a different VM as well). One will likely be faster than the other, even if the languages perform elementary operations at the same speed, because they will be using different libraries to even deal with things as basic as strings.
- Different database engines: most of the previous point applies here, because each will likely process the same SQL query differently, the same issue as having two different compilers. Then you have to consider they are two entirely different code bases, so it's pretty unlikely every operation takes identical time in each.
The real issue here is, you can't figure out which component is the bottleneck because you're comparing 3 different variables at once.
Now if you were trying to ask which is faster, if I took a shot in the dark I'd guess case 2, C# seems to general perform better than Java in my experience, and paid software with big corporations behind it (i.e. Microsoft being behind Windows, .Net, and MSSQL Server) tends to outperform open-source and other free software, simply because they can pay developers to work on it full-time. Free or open-source doesn't always have that luxury. (And obviously, this is not always the case, I could be completely wrong here.)
|
|
|
|
|
You have very good points regarding 3 variables and hard to measure, but I don't totally agree Microsoft products are fast simplify because Microsoft can pay. Java and mysql are supported by Oracle and indirectly attack Microsoft, just like how andriod attack apple, even saying it is open source.
|
|
|
|
|
I doubt Oracle is going to put much into MySql when they had their own line of databases already, which seem to be much more favored in the business world. I think I may not have driven home the "free" vs "paid" point as heavily as I intended. A paid product generally has more money going into it (and open source tends to be free because you can't charge much when anyone can just download the source and compile it, so I often lump them together). It's simple economics, a product that doesn't generate much money can't have much cost behind it or it isn't profitable. Android may be free and open source, if Google wasn't making money on it, they wouldn't bother (consider: Google Play, ad supported free apps using Google's ad service, and more directly a few phones, especially now that they own Motorola Mobility, not to mention they probably charge companies using Android for support).
Also, Java may have had Sun and now Oracle behind it, but the whole thing feels pretty sloppy. Every time an update rolls around, our development crosses our fingers and hope it doesn't break something in our Java applications. Just today we found a bug in 7u25 that stops images from rendering...until Java is launched a second time. As I doubt out image rendering was using a security exploit or a bug in Java, there is no reason a security update + bug fix should have caused this. We had similar issues with the last update (at least the changes that caused that were documented, so we were able to fix it before any or our clients came across it).
We have never have these problems with out .Net applications. I honestly think Microsoft has put a lot more money and effort into their .Net platform.
|
|
|
|
|
Can the java team in your corp chooses to not update java, or test the updated version of java before running it with live system. Java update and affect the program is a possible big issue, but running a stable .net program in a windows version then later that windows is not supported anymore costs even more big problem. Let's say I run a program in XP age then now XP or that age windows server is not supported or updated, if I run the same .net program in current windows may has serious issue.
MS sql is more supported and stable, very friendly ide. For my trading program, I just use the mysql to store the data then only the program is running, I pull all data instead array and do calculation with arrays, so I don't need very advanced database functions.
I am going to use IB api and they have socket for java but only activex for vb/.net, and no example for c# at all, this is a big concern since even they official wrote there could be event lose with activex, that's mean if my trading program is automated later and trading at night while I sleep, it can have a buy order then a sell order event lost...then my future just up and down for awhile with luck.
|
|
|
|
|
crunchor wrote: Can the java team in your corp chooses to not update java, or test the updated version of java before running it with live system.
We can choose not to, but that doesn't mean our clients will listen. Especially since Oracle has decided to pop up a scary (from the user's perspective) warning message about their current Java being insecure, starting with 7u21. And it can't just be ours, the change that broke some of our code in 7u21 was bad enough that they added an option in 7u25 to disable the changed functionality. Not improved, or bug-fixing, as far as I can tell, they just decided that this existing function should suddenly behave differently in a patch. We've also found and reported bugs in Java itself, which were in turn acknowledged by Oracle (may have been Sun at the time) as a bug, and then told that they weren't going to be fixed.
In the .Net world, we have stuff originally written to run on .Net 1.1 that is still chugging along today, on top of various version of .Net form 2.0 to 4.5, with the only bug fixes being relating to the code we wrote, instead of the platform changing underneath it, and much more time to make enhancements. On the other hand, Java code written and compiled against Java 6 can't be guaranteed to run on the Java 7 VM the next day. (Obviously, we'd like to compile against Java 7 and gain access to the new features, but there's a lot of testing we have to do first.) I think the best example of the issue is this: for every version of our Java software, we release a list of supported Java versions, and it's down to the specific update of that version (e.g. there were a few updates of 6 we couldn't support, because of breaking changes in Java), but for anything .Net based, we only need to specify one version, and that's the minimum version (usually 3.5, because of new features added there that we use heavily). There's never a report of "hey, I updated .Net and now this program stopped working, but if I downgrade back to the previous version it works just fine", but with the Java stuff that is probably a semi-monthly occurrence.
crunchor wrote: Let's say I run a program in XP age then now XP or that age windows server is not supported or updated, if I run the same .net program in current windows may has serious issue.
If Java updated as infrequently as you change operating systems, this might be a viable concern. And the only bugs in .Net apps I've come across that render them incapable of running on a newer version of Windows have been primarily from changes in the file structure (e.g., the move from using [drive]:\Documents and Settings\ to [drive]:\Users), which probably shouldn't have been hard coded in the first place, or to changes in file permissions (especially the fact that by default, Program Files can no longer be written to). I haven't seen any .Net related issues leading to this.
crunchor wrote: For my trading program, I just use the mysql to store the data then only the program is running, I pull all data instead array and do calculation with arrays, so I don't need very advanced database functions.
Then I don't see how the performance even matters if this is a once-per-execution event. Even assuming differences in perofrmance, they aren't likely to be enough to really matter once your program has finished execution (will probably also effect initial load times, but probably not substantially).
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
