Another DBA Whoops
-
Remi, I still work with my partner and his comment is "You should have seen Jim's face, I just felt sorry for him".
-
I can imagine the face
. -
Wenck, dates are only a problem when not used correctly. Most programmers are inclined to use the easier route of programming ODBC and JDBC using basic SQL strings rather than using proper SQL command objects when interfacing with Stored Procs or executing queries.
The problem with using SQL strings is that the SQL pre-compiler has determine what format a date string is, based on the date format currently in use in the database where the SQL is executing. If you need to follow this approach you should always format the date in the string being passed to either "YYYY-MM-DD" (ISO) or to one of the long formats like "MMM DD YYYY" or "DD MMM YYYY". By forcing the month part to text the parser knows exactly which part is the month.
The more robust approach is to use a command object and append to the values for parameters to the variable holders (question marks).
eg. Insert into myTable(mytext, mydate) Values (?,?)
There are different way to append the parameters depending on the language and API but usually all refer to the ? marks numerically and you also specify the datatype and obviously the value. The other advantage of calling SQL this way is that most SQL pre-compilers are smart enough to recognise that repeating the same command with new parameter values is not a new command and there is no need to re-compile thus saving a small amount of processing time.
-
To preempt another potential whoops (it got me once) :
A programmer may decide to circumvent the date format issues by holding dates as Julian day numbers. Unfortunately, SQL Server bases its day count on 1 January 1900 but VB uses 31 December 1899. Oops!
... and yes, I would suggest: always use parameters with ADO, avoid dynamic SQL as the plague and only pass recordsets when unavoidable.
-
This one as well as the solutions posted will stay in my mind, since indeed Regional settings have propably costed me most troubles as a programmer.
Not only the switch of date data, but also the decimal seperator.
I once wrote a weekstatistic producing module in visual basic. I wrote and installed it after the 12th day of a certain month. Everything went perfect between the 13th and the 31th, but once it became the first of the next month ... the statistics went mad. In Belgium we normally use dd/mm/yyyy but since the day after 31/07/2005 became 08/01/2005 -it's a strange world- I started using yyyy-mm-dd everywhere where possible, not only in code, but also in text, in filenames, ... because yyyy-mm-dd always seem to sort logically, also in numerical and alphanumerical (non date) data types.
In Belgium we use comma instead of points to seperate decimal data. This is even worse since I've experienced some of our financial data being multiplied and sometimes being divided with factor 100 while using 2 decimals. So we can pass a VB datatype double to a stored procedure accepting a decimal data type without problem, but when I paste the same double in a query build on the fly inside my VB-code (I know it's not by the book) we have to cast to strings and replace points and commas. This is fine as long as none of our users find out the meaning of the meaning of NL/EN icon in the taskbar and as long as nobody by accident presses CTRL-ALT-SHIFT causing locales switching.
Perhaps we'd better always check the current regional settings in our code when using decimal and date data.
We've also experienced big regional setting troubles on website level till we found out our website users inherit regional settings from a specific windows account, which might have other regional settings than the accounts we use to log in, to develop and to maintain our servers.
My whoop of this week, not bad either :
I wrote a lot of queries, including table dropping, truncate table, ... and executed them on a test database. I saved all the queries together in one .sql file. Afterwards I wanted to execute SOME of those queries on our life database. At once we (after some users reporting strange things) dedected empty tables, we saw new tables which shouldn't be there, ...
At the first moment I thought we'd been hacked, but than when I started analyzing all "strange" things in our database I realized I must have pressed F5 in the query analyzer, while no specific query was selected. So from now on I put the following statement at top of all my .sql files ...
loop while 1 = 1
begin
print getdate()
end
Now, when the red icon says "execution in progress", I no longer have to fear I've pressed F5 by accident. I no longer fear top quality Keyla cleaning my unlocked keyboard while I'm looking for thea in the kitchen.
We can't blame Microsoft there are different defaults, can we?
Kind Regards, Peter Van Wilrijk.
-
I think of this as the classic “what the data looks like” vs. “what the data is” situation. I advise thinking of it this way:
1) When SQL is working with a datetime (or smalldatetime) value, it’s working with an 8 byte (or 4 byte) chunk of binary data. Wifty stuff, really—number of days since X, number of seconds since Y, check out BOL for details.
2) Of course, this is completely illegible to humans, so we have to represent it—that is, transalte it—into some fashion that we can understand. Being humans, we naturally can’t agree on any one way to do this, so we get saddled with MM/DD/YY, YYYY-MM-DD, MMM DD, YYY [hh:mm[:ss[.sss]]], Y2K, and on and on and on. The key thing is, all of these are character strings. Character strings are not “time data”, they are character strings and must be dealt with as such.
Care—if not extreme care—must be taken whenever translating in either direction between character data to time data. It is never a trivial problem, if only because of the foul-ups that
canwill occur when it’s messed up. (Classic one: our systems went screwy once when we hit the new year. Not because the year changed, but because the month flipped from 10, 11, 12, to 01… months after the “lead” developer’s code was deployed.)The quick way to think of it: within this context, time data is time data, and string data is pretty labels. What matters more, data or pretty labels? When it comes to billing, I know what I'm going to use.
Philip
-
Along the same lines as Philips response, we never format dates inside the database unless absolutely necessary, usually because we have to change the stored procedure/view anyway, and because of time pressures, don't want to change the code either. Otherwise, it is the client application's responsibility to present the dates as visually appealing strings in the UI, and they usually have a wider range of options for doing so. Databases should only be concerned with storing/retrieving the content.
Interestingly, though, I can't seem to find a way to meet all three requirements with this problem:
1) Retrieve the same data set
2) Format the date as mm/dd/yyyy
3) Keep the final column names the same
Any ideas here?
-
See my post (No 9) on the first page of this thread.
-
Why don't use like this, to get the desired results...not affection query :
select orderid,customerid,convert(char(10),requireddate,101) as requireddate
from (
select top 5 orderid,customerid,requireddate from orders
order by requireddate desc) ord5
Thanks.
Naras.
-
ah, yes, thank you
-
My own personal WHOOPS (One of many)
I once wanted to DELETE from tblUSERS WHERE .... Unfortunately, I had the command on multiple lines and highlighted the DELETE FROM ...
Needless to say ALL the users were DELETEd.... Luckily we had replication running (every 5 minutes) and I was able to recover quickly.
First thing I did was yell for my boss to help. He was able to engage others to help pull the data from the replicated box and save it while we sent e-mails out letting everyone in the company know what I did.
I earned the "golden shoehorn" (help me pull my head from the 4th point of contact)
--------------------------
Last week I helped another associate get over the fact that they did the same thing.
Best advice I gave him. Don't dwell on it. Disclose you did it and learn from it...
Good Hunting!AJ Ahrens
webmaster@kritter.net -
"golden shoehorn"
What's that?
-
On a side note, I think that in the original query if you qualify the sort column with the table name - sql should know to sort on the table value not the converted value from the select list. I KNOW that it doesn't, but I'm merely saying that I feel sql should behave that way.
Anyways, its an easy mistake to make. Simple to fix (and meet the requirements outlined below):
select top 5
orderid
, customerid
, convert(convert(char(10),requireddate,101) as requireddate
from orders
order by convert(smalldatetime,requireddate) desc
---
CDJorg says:
Interestingly, though, I can't seem to find a way to meet all three requirements with this problem:
1) Retrieve the same data set
2) Format the date as mm/dd/yyyy
3) Keep the final column names the same
-Ken -
Nice to know I have some company
It's funny how things that you do regularly come back to bite you when you twist them just a little one way. It goes to show that having a good set of iron clad rules, like never reformat dates in the db, can really help.
If you've got some good stories, especially a page or longer, send a draft over, even a plain text file and I'll help you get them published. We can even do it anonymously if you like.
-
You really don't wanna be alone in this do you
.Maybe it's time to do another nightmare contest...
Viewing 15 posts - 16 through 30 (of 58 total)
You must be logged in to reply to this topic. Login to reply