Another DBA Whoops
-
This is definitely one of the Software Architecture Whoops rather then a DBA whoop
The datetime display format is definitely an UI issue...
So the UI developer needs to take care of that in the UI layer rather than trying to format it in the DB... and preferrably in a way that supports different cultures...
The datetime is only one of the problem fields, try formatting the number fields in a US database, and displaying them in France... You will definitely get "Funny" results if these are not invoiced amount numbers, and probably lawsuits if they are
Thanks
Duray AKAR
-
The other option is that if you're not worried about the column name, you simply leave the AS out of the query:
select top 5
o.orderid
, o.customerid
, CONVERT(char(10), o.requireddate, 101)
from orders o
order by requireddate desc
In query analyzer, this will show (no column name) in the return set, but there are ways to work around that.
I agree with the last post, this is a display issue, and the date should be converted upon display, rather than in the query.
-
Remi,
The "Golden shoehorn" was started by our company to go with the "Golden tractor" that our Network Administrator had.
It literally is a shoehorn spray painted gold that you have to display PROUDLY. Another thing is IF you are asked you have to tell WHY you received it.
The shoehorn was for MINOR offenses that were quick to be corrected. The tractor was for MAJOR problems. The rules for the tractor were the same as the shoehorn.
It actually helped with problems. No one wanted either of them (Except for a person that wanted the "golden bulldozer" (tractor with the shoehorn on the front).
It acted like a poor man's QA process. Once things were coded we internally tested (didn't have a test department/team(s)) and performed more extensive testing.
Good Hunting!AJ Ahrens
webmaster@kritter.net -
I see Stewart Joslyn came up with the same answer as I did. (I "created" his second solution.)
I also agree with spongemagnet AKA "Ken" that SQL should know better. When you specify the original source, SQL should use the original source. I first tried:
select top 5
o.orderid
, o.customerid
, CONVERT(char(10), o.requireddate, 101) as requireddate
from orders o
order by o.requireddate desc
because I didn't know what spongemagnet did, that it doesn't work. Then I came up with Stewart's solution. Never do what I did next
on production deployment for any reason:select top 5
o.orderid
, o.customerid as requireddate
, CONVERT(char(10), o.requireddate, 101) as customerid
from orders o
order by o.requireddate desc
Yup, it sorts by the internal o.customerid field. With my next test, I know it's a bug. With a join, sorting by requireddate with or without the o. prefix has the same result as before, but sorting with or without the prefix on customerid produces completely different results
:select top 5
o.orderid
, o.customerid as requireddate
, CONVERT(char(10), o.requireddate, 101) as customerid
from orders o join dbo.Customers c on c.customerid=o.customerid
order by customerid desc
Now SQL does know the difference between a local field and an internal field with the same name. If an application acts inconsistently, it's a bug, otherwise it might just be a (in your opinion) bad "feature". (My company provides plenty of the latter.
) -
Sounds like a problem with your application, not SQL Server.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Well, it's the number one problem that I constantly read about. When I use to subscribed to SQLServer Mag, there was a couple of articles in there about.
-
How many times did you get it ??
. -
I received the "award" once. Probably deserved it a couple more times but other people made larger mistakes
Good Hunting!AJ Ahrens
webmaster@kritter.net -
May we hear the story
. -
This would be enough:
select top 5
o.orderid
, o.customerid
, CONVERT(char(10), o.requireddate, 101) as requireddate
,o.requireddate rd
from orders o
order by rd desc
Since any single client may ignore the existence of rd column in their result sets.
Al least, any dblib and oledb client (which I think is all, since any other depends on them).
-
simplest way to sort out the problem.
Just use
order by o.requireddate desc
not
order by requireddate desc
by specifying the table name the query will use the field in the table to order the query and not the calculated field.
Just remeber SQL orders dates in the same way it orders text strings. Byte for byte from left to right. This is why SQL stores date time variables in the "yyyy-mm-dd hh:mm:ss" format, because this stores the data in a most significanct bit to least significant bit order. Basically it takes the first byte of the date and will order everything according to that, then take the next byte and within the ordered subset order everything according to that. In many respects it a very efficient way of doing it. No complicated calculations, just store the date in the correct format and away you go.
A piece of advice to developers have trouble with dates it might be an idea to write them as strings to the database formatted in the correct SQL format. In VB the command would look like this :
DateVarString = Format (DateVar, "yyyy-mm-dd hh:nn:ss")
SQL will accept string type data into the datetime datatype as long as its formatted correctly.
Hopes this helps
-
Passing dates as strings assumes the dateformat on the target and this cannot be guaranteed unless, without fail, all your stored procedures include SET DATEFORMAT (which any of mine that involve date strings for any reason do!). It is dangerous to assume anything about configurable items, especially date strings. Even if you have rigid control of the environment, you cannot assume that that always will be the case ... and what happens when you have moved on and somebody doesn't realise how critical a particular setting is to your application?
-
Anthony, nice try but no cigar. I thought of the same thing myself but it doesn't work. It still orders by the alias.
Regarding your second statement this is also wrong. SQL only displays date fields in the yyyy-mm-dd hh:mm:ss format, they are stored and worked on behind the scenes as a decimal value. The left only the decimal is a count of days from January 1, 1753 and the right of the decimal is the time (24 hours of a day represented as a decimal fraction). Ordering is done numerically. Writing dates as strings impedes performance, causes table sizes to be unnecessarily large and causes indexes with dates in them to be large and cumbersome. It also results in double converting or casting in order to manipulate them.
-
I think that the orginal article is a classic mistake. I didn't see the error at first because the issue with the column name distracted me, it is odd that the alias name of the column would affect the sort order. You, must however, put the conversion of the date not only on your column but in your order by clause as well. Usually this is a forced issue because you can use the alias in your order by clause, in your case your alias and your column name were the same so it was easy for you to just drop the column name into the order by clause with out the convert statement.
With this of course you have the added problem of strings which affect the order. To get around this I set the time to midnight without converting it to a string.
I do this sort of thing all the time because I have to force the time to midnight on many of my reports. So I have a function that automatically strips the time off. In doing so I have to include my function in my group by clause or else it will include the time with the date an thus give me hundreds of records for each day when I am only looking for a one line summary.
Thanks for bringing the weird alias name issue to light. My manager says he sees something simular when working with access.
--- Keith
-
Ahhhhhhhhhhh :0
Managers and Access. Keep them away!!!!!
Viewing 15 posts - 31 through 45 (of 58 total)
You must be logged in to reply to this topic. Login to reply