Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
parrot
Posting Yak Master
132 Posts |
 Posted - 2008-08-12 : 15:34:36
|
| How do you do a sort on a date field. If I use the following statment,SELECT BIRTH-DATE EMPLOYEE ORDER BY BIRTH-DATEThe date field is sorted like a regular field rather than a date field in that it would show the following order:01/01/199202/15/197503/09/1980etc.Anyone have an answer on how to correctly sort date fields? I couldn't find an answer in SQL Books Online.Dave |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-12 : 16:23:17
|
ORDER BY CONVERT(DATETIME, [BIRTH-DATE], 101)
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2008-08-12 : 16:41:27
|
| Thanks to both who replied. The BIRTH-DATE field is stored as a datetime field on the data base and I thought the query would recognize that when it sorted the field. Evidently not, so I use the CONVERT(DATETIME,BIRTH_DATE, 101) suggested by Peso and it worked. Thanks again.Dave |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-12 : 16:44:23
|
Well, what can I say?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-12 : 16:48:32
|
| I don't believe it's stored as datetime data type. You wouldn't have this sorting problem, unless you haven't posted the whole story.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2008-08-12 : 18:01:09
|
| My original query looked like this when I did some conversion.SELECT EE.Employee, CONVERT(char(10), EE.BirthDate, 101) AS "BirthDate" FROM EE ORDER BY BirthDate The birthdate field is defined as datetime in the database. What was happening is that I converted it to character to display the format I wanted and then it sorted on the new converted name which was by now converted to character. I changed the char(10) to DATETIME and it took care of the problem. However, there is a new twist now. If I use the DISTINCT command with my query and I want to display a date converted with the 107 value which is monthname dd, yyyy, I receive an error saying that ORDER BY items must appear in the select list if a SELECT DISTINCT is specified. SELECT DISTINCT EE.Employee, CONVERT(char(12), EE.BirthDate, 107) AS "BirthDate" FROM EE ORDER BY CONVERT(DATETIME, EE.BirthDate, 101)I haven't figured out how to get around this limitation. In other words, I wish to display a date in a different format that the one I want to sort on which I can't do with the DISTINCT command.Dave |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-12 : 18:04:13
|
| Aha, so you converted to char(10) in your query which caused the issue!To get around the conversion in the order by, you can use a different alias name:SELECT EE.Employee, CONVERT(char(10), EE.BirthDate, 101) AS BirthDt FROM EE ORDER BY BirthDate Or better yet, do not do a conversion at all, as this should be done in your application due to it being a presentation issue.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
|
|
Sorting dates