Order By Date issue - SQL2k5

Order By doesnt order away from 1/1/1900

Page 1 of 1

3 Replies - 975 Views - Last Post: 23 July 2009 - 02:17 PM Rate Topic: -----

#1 woodjom   User is offline

  • D.I.C Addict
  • member icon

Reputation: 34
  • View blog
  • Posts: 566
  • Joined: 08-May 08

Order By Date issue - SQL2k5

Post icon  Posted 22 July 2009 - 12:57 PM

I have a slight quandry. When i do a USP again my database, i produces an unordered list, as expected (First Come, First Served out). But when i add the Order By clause to the statement it order by Month, then Day, then year. How do get the query to ordery by Year, Month, Day without changing the underlying data in the field?

Current USP:
ALTER PROCEDURE dbo.usp_SELUserTasks_ALL
	(
	@userid bigint
	)
AS
	SELECT 
		/* Hidden Fields */
		id,
		pid,
		tdueon,
		o_admin1,
		o_admin2,
		/* Visible Fields */
		pname,
		tid,
		descript		
	FROM fn_GetUserTasks_ALL(@userid)
	where pcomplete=0
	order by tdueon



Current Output w/Order By Clause:
id					pid				   tdueon	 o_admin1			  o_admin2			  pname											  tid				   descript																																																														 
--------------------- --------------------- ---------- --------------------- --------------------- -------------------------------------------------- --------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
4					 2					 06/10/2009 1					 -1					Test 2											 1					 NOTHING																																																														  
5					 2					 06/10/2009 1					 -1					Test 2											 2					 NOTHING																																																														  
26					15					07/23/2003 3					 -1					Tim's First Project								1					 Test This task 1																																																												 
19					11					09/01/2009 1					 3					 Test 64											1					 Create a new test																																																												
20					10					09/01/2009 1					 3					 Test 63											1					 test 3 7/22/09																																																												   
11					6					 09/01/2009 1					 -1					Test453											2					 a																																																																
13					6					 09/01/2009 1					 -1					Test453											4					 c																																																																
14					6					 09/01/2009 1					 -1					Test453											5					 d																																																																
15					6					 09/01/2009 1					 -1					Test453											6					 e																																																																
16					6					 09/01/2009 1					 -1					Test453											7					 f																																																																
18					12					09/01/2010 1					 3					 Test 65											1					 abc																																																															  
8					 6					 12/01/2009 1					 -1					Test453											1					 Test																																																															 
27					16					12/01/2009 1					 3					 Test 75											1					 something																																																														


Am i missing something? i have tried ASC and DESC and both order by the same process, Month, Day, then Year, irregardless of which direction i want it to go.

Is This A Good Question/Topic? 0
  • +

Replies To: Order By Date issue - SQL2k5

#2 woodjom   User is offline

  • D.I.C Addict
  • member icon

Reputation: 34
  • View blog
  • Posts: 566
  • Joined: 08-May 08

Re: Order By Date issue - SQL2k5

Posted 23 July 2009 - 07:19 AM

Got if figured out....although i think other sites go way too deap to get the sorting done.

Changed USP as follows:
SELECT 
		/* Hidden Fields */
		id,
		pid,
		tdueon,
		o_admin1,
		o_admin2,
		tcomplete as complete,
		/* Visible Fields */
		pname,
		tid,
		descript		
	FROM fn_GetUserTasks_ALL(@userid)
	WHERE tcomplete = 0
	ORDER BY
		DATEPART(Year,tdueon), 
		DATEPART(Month, tdueon), 
		DATEPART(Day, tdueon)



Gets it sorted just the way i want.

Wish i could thank myself :)

This post has been edited by woodjom: 23 July 2009 - 07:19 AM

Was This Post Helpful? 0
  • +
  • -

#3 ForcedSterilizationsForAll   User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: Order By Date issue - SQL2k5

Posted 23 July 2009 - 01:29 PM

View Postwoodjom, on 23 Jul, 2009 - 06:19 AM, said:

Got if figured out....although i think other sites go way too deap to get the sorting done.

Changed USP as follows:
SELECT 
		/* Hidden Fields */
		id,
		pid,
		tdueon,
		o_admin1,
		o_admin2,
		tcomplete as complete,
		/* Visible Fields */
		pname,
		tid,
		descript		
	FROM fn_GetUserTasks_ALL(@userid)
	WHERE tcomplete = 0
	ORDER BY
		DATEPART(Year,tdueon), 
		DATEPART(Month, tdueon), 
		DATEPART(Day, tdueon)



Gets it sorted just the way i want.

Wish i could thank myself :)


You can also sort by YEAR(tdueon), MONTH(tdueon), DAY(tdueon) or at least you should be able to.
Was This Post Helpful? 0
  • +
  • -

#4 woodjom   User is offline

  • D.I.C Addict
  • member icon

Reputation: 34
  • View blog
  • Posts: 566
  • Joined: 08-May 08

Re: Order By Date issue - SQL2k5

Posted 23 July 2009 - 02:17 PM

True but if i remember what i read....the Year, Month and Day functions are there as simple tools but still refer to DatePart function. So therefore, by using the DatePart function initially i take a very very very small cut off the processing time of the data :)

I try to use the base types as much as possible to keep everything as clean and smooth as possible.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1