Problem with sorting date(part 2)

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with sorting date(part 2)

Author

Topic

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2005-01-28 : 13:14:38

I originally posted a similar message about this problem in the "General" forum but as I looked more into my issue, I realized there's much more involved than a simple query issue as I had mentioned in my previous post.

First off, I know this is a long winded post but I greatly thank anyone who is able to help out as I'm rapidly approaching my deadline and MUST have this portion of my application working properly.

My problem is that I need to format a field with a datetime datatype to read something like "12-25-2004" as opposed to "2004-12-25:11:34:04.104" however when I return this data back to my application, it needs to already be sorted in chronological order and for some reason, because I'm converting this "datetime" into a "varchar" to format it, I can't sort it properly.

I've tried to organize my issue related code listed below as best I can. If you're unclear about anything please let me know.

To briefly explain what's going on, this query is part a stored procedure and within this stored procedure, I create a temporary table, then declare a cursor, use a query to find the necessary data within the cursor, insert the necessary data from this cursor into the temporary table, and then select all rows in the temp table to be returned to my application.

So the potential trouble spots look like this:

1) I first declare a variable for the date each row in the table was added:
--------------------------------------------------------
declare @AddDt varchar(10)
-------------------------------------------------------

2) I then create a temporary table(the "...." represents other fields which are irrelevent to this date issue:
-----------------------------------------------------------------
Create Table #TBReadNote(.... , .... , ...., AddDt varchar(10), ....)
-----------------------------------------------------------------

3) Next, I declare my cursor:
-----------------------------------------------------------------
DECLARE Note_Cursor Cursor READ_ONLY FAST_FORWARD For
Select ...., ...., convert(varchar(10),cnAddDt,110) as AddDt,.... from TableName ...... order by AddDt
Open Note_Cursor
Fetch Next From Note_Cursor
Into ...., ...., @AddDt, ...., ....
While @@FETCH_STATUS=0
Begin
-----------------------------------------------------------------

4) Later in the cursor, once I've found all of the necessary info, I insert all of it into my temp table:
-------------------------------------------------------------------
Insert Into #TBReadNote(...., ...., AddDt, ...., ....)
Values(...., ...., @AddDt, ...., ....)
-------------------------------------------------------------------

5) Finally, I select all of the rows in my temp table and try to order it by the "AddDt"(which is where the problem is):
------------------------------------------------------------------
Select ...., ...., AddDt as AddDt, ...., ....
from #TBReadNote
order by AddDt desc
-----------------------------------------------------------------

So what I end up getting are dates which are formatted properly however when my stored procedure goes to sort the rows by this date field, it interprets them as string data and thus my row order looks like this:
'01-12-2004'
'01-12-2005'
'05-15-1999'
'10-18-2004'
'12-01-2002'
'12-01-2004'
'12-01-2005'

Any idea on how I can fix this?

I'd GREATLY appreciate any help you can provide.

-Goalie35

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-01-28 : 13:27:20

Yep, cast it to datetime in your ORDER BY


DECLARE @MyTable TABLE(MyDate varchar(10))

INSERT INTO @MyTable(Mydate) VALUES('01-12-2004')
INSERT INTO @MyTable(Mydate) VALUES('01-12-2005')
INSERT INTO @MyTable(Mydate) VALUES('05-15-1999')
INSERT INTO @MyTable(Mydate) VALUES('10-18-2004')
INSERT INTO @MyTable(Mydate) VALUES('12-01-2002')
INSERT INTO @MyTable(Mydate) VALUES('12-01-2004')
INSERT INTO @MyTable(Mydate) VALUES('12-01-2005')


select * from @MyTable ORDER BY CAST(MyDate as DATETIME)

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-01-28 : 13:30:32

Oh yeah, post all of your code and maybe we can help you get rid of that cursor. Those things are to be avoided at all costs.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources