SELECT Help
-
Hi,
I have a table A which is having the following data
BirthDate 12/1/2005 12/20/2005 12/17/2005 12/18/2005 12/19/2005 12/28/2005 1/1/2006 1/2/2006 7/29/2005 7/30/2005 7/31/2005 8/1/2005 8/2/2005 8/3/2005 12/19/1950 When I select I need the following order
7/29/2005 7/30/2005 7/31/2005 8/1/2005 8/2/2005 8/3/2005 12/1/2005 12/17/2005 12/18/2005 12/19/2005 12/19/1950 12/20/2005 12/28/2005 1/1/2006 1/2/2006 Is there any way to do with SELECT statement?, Any help would be appriciated.
Thanks!.
-
What's the datatype of the column ?
-
What DATATYPE is your column? If it's VARCHAR, it will sort by dictionary sort.
1
11
12
2
3
4
etc.
If it's DATETIME, you could try:
SELECT CONVERT(VARCHAR(10), BirthDate, 101)
FROM tablename
ORDER BY BirthDate ASC
-SQLBill
-
If it's VARCHAR, you might try:
SET MDY
SELECT BirthDate
FROM tablename
ORDER BY CONVERT(DATETIME, BirthDate) ASC
-SQLBill
-
Its DATETIME.
SQLBill,
Your query
SELECT CONVERT(VARCHAR(10), BirthDate, 101)
FROM tablename
ORDER BY BirthDate ASC
doesn't work.
-
Does it give an error or just return the data incorrectly?
_SQLBill
-
How about just accurately telling us the requirements, instead of presenting a table of data and playing a game of "go fish" while we all guess at what's actually required ?
eg: It looks like you want to sort by month first, but then the January 2006 data doesn't seem to be in the correct place, so maybe it's year first, but nope, then the year 1950 is in the wrong place. Huh ?
-
SQLBill,
It returns in wrong order.
Mr. or Ms. 500,
I need the dates in order but i don't want to consider year.
Thanks.
-
>>I need the dates in order
Yes. But in what order ?
Why is January coming after December, if July & August are coming before December ?
-
I don't understand ur question.
-
And I don't understand your "answer"
You gave us a table, containing dates the way you'd like them ordered. In that table, we see "7/31/2005" sorted before "12/17/2005". Month 7 before month 12.
If 7 is less than 12 and 7 must come before 12, please explain the last 2 rows in your table, including "1/1/2006". Why does 1 come after 12, if you've already stated that the year doesn't matter ?
-
When I said year doesn't matter means
12/19/2005 12/19/1950 Jan comes after Dec thats why I have 1/1/2005,1/2/2005 at last position.
-
>>Jan comes after Dec thats why I have 1/1/2005,1/2/2005 at last position.
So ? July comes after December too, yet you have July dates *before* December dates in your example.
-
This might have something to do with Financial Year Starts. If July 1st is the start of the fiscal year then July would come before December, but January would come after December.
But I don't really see why the year doesn't matter then... Surely you would need to know that as well, if dealing with fiscal dates.
I once worked for a company who's fiscal started in October, had 12 working months in a fiscal year, but each month had to start on a Monday (or the first non Bank Holiday after a Monday), so two ‘months’ were arbitrarily made 5 week months. But for reporting purposes you needed to show month of the year and week of the month (in fiscal year terms) – made creating the reports that more fun…
-
OK if we're playing at guessing the query
I got out my ouija board and got this
SELECT BirthDate
FROM #a
ORDER BY CASE WHEN MONTH(BirthDate)=1 THEN 1 ELSE 0 END,
MONTH(BirthDate),DAY(BirthDate)
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply