Dynamic ORDER BY using datetime column

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

Transact-SQL (2000)

Dynamic ORDER BY using datetime column

Author

Topic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-10 : 18:55:23

Yes I have read the dynamic ORDER BY article (also read the comments, also searched the forums), but I still can't seem to figure this one out.

CREATE TABLE Table1
(
Column1 VARCHAR(50) NOT NULL,
Column2 DATETIME NOT NULL,
Column3 int NOT NULL
)

INSERT INTO Table1 VALUES('Zzzuuu', '2003-02-12 09:56 AM', 9)
INSERT INTO Table1 VALUES('Aaaeee', '2003-03-01 11:20 PM', 3)
INSERT INTO Table1 VALUES('Something', '2001-12-12 08:23 AM', 24)
INSERT INTO Table1 VALUES('Else', '2000-02-12 09:56 AM',1)
INSERT INTO Table1 VALUES('You', '2003-02-11 09:56 AM', 99)

DECLARE @sortOn varchar(100)

SET @sortOn = 'Column2'

SELECT Column2
FROM Table1
ORDER BY
CASE
WHEN @sortOn = 'Column1' THEN CAST(Column1 AS VARCHAR(50))
WHEN @sortOn = 'Column2' THEN CAST(Column2 AS VARCHAR(50))
WHEN @sortOn = 'Column23' THEN CAST(Column3 AS VARCHAR(50))
END ASC

SELECT Column2
FROM Table1
ORDER BY Column2

DROP TABLE Table1

The above is just an example. The second SELECT shows what I want the first SELECT to output. But how do I do that when the CASE statement is using different data types and one of them is datetime. The comments for the article says to CAST/CONVERT the columns to a common data type such as varchar, so that's what I did. But when you convert a datetime column to varchar then sort on it, it doesn't sort it correctly (well it sorts it correctly for a varchar column but I want it to sort it like it was still a datetime column).

What can I do to fix this? My only thought was to do this in an IF statement:

IF @sortOn = 'Column2'
ORDER BY Column2
ELSE
ORDER BY
CASE
WHEN @sortOn = 'Column1' THEN CAST(Column1 AS VARCHAR(50))
WHEN @sortOn = 'Column3' THEN CAST(Column3 AS VARCHAR(50))
END ASC

Is there a way to do it without the IF?

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-10 : 19:00:03

I didn't check the comments from the article, but I swear I've posted this before somewhere:

select charfield, datefield, intfield
from
table
order by
case when @sort = 'charfield' then charfield else '' end,
case when @sort = 'datefield' then datefield else '1/1/1900' end,
case when @sort = 'intfield' then intfield else 0 end

That should do the trick for you.

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-10 : 19:51:35

Thanks Jeff. It works perfectly. It took me a little bit to figure out why it works, but I think that I get in now. Thanks again!

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-10 : 20:27:34

This also seems to work:

WHEN @sortOn = 'Column2' THEN CONVERT(varchar(50), Column2, 21)

Is there a downside to doing it this way?

Tara

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-04-10 : 21:32:41

Dates may not convert in a way that sorts properly, numerics also won't sort correctly because they won't have leading spaces. One variation on Jeff's technique that should solve that problem:

select charfield, datefield, intfield
from table order by
case when @sort = 'charfield' then charfield else Null end,
case when @sort = 'datefield' then datefield else Null end,
case when @sort = 'intfield' then intfield else Null end

That way you'll sort numerics and dates properly without having to convert anything, and it should also let the optimizer use any indexes that might be available.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-04-10 : 22:04:43

Just thought I would chip in with something slightly different...

Cast everything to VARBINARY and you can use a single CASE statement


SELECT * 
FROM Table1 
ORDER BY 
CASE @sortOn
	WHEN 'Column1' THEN CAST(COLUMN1 as VARBINARY(50))
	WHEN 'Column2' THEN CAST(COLUMN2 as VARBINARY(50))
	WHEN 'Column3' THEN CAST(COLUMN3 as VARBINARY(50))
END ASC

DavidM

"SQL-3 is an abomination.."

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-11 : 08:05:55

Rob -- I missed the difference between ours ... does mine have a problem with leading zeroes or dates ??

- Jeff

Edited by - jsmith8858 on 04/11/2003 12:17:29

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-04-11 : 08:20:43

Tara,

Remember that dates won't sort properly until in the yyymmdd format.

Same problem with numbers....sorting in a varchar treats everything as a string....and thus all numbers number be in the same format...with sufficient leading zeros applied to make them standard.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-11 : 08:24:23

Andrew -- that was her question in the first place -- how do you handle dates and numbers in the same column as strings?

And the answer is, you don't, you just sort them as seperate columns -- one for each datatype. and then there are no problems or conversions to worry about, as in the solutions that Rob and I gave.

(note there are commas between each case expression in the ORDER BY -- it is essentially sorting on 3 different values, but 2 of them are always constants)

- Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-11 : 09:28:12

Just a comment - doesn't add anything really.

The reason you get an error is because of the data type precedence - datetime being higher than string it tries to convert everything in the order by field to datetime. Better than implicitly converting everything to string and not getting an error but sorting incorrectly.

You could
SELECT Column2
FROM Table1
ORDER BY
CASE
WHEN @sortOn = 'Column1' THEN Column1
WHEN @sortOn = 'Column2' THEN convert(varchar(50,Column2,112)
WHEN @sortOn = 'Column3' THEN right(replicate(' ',50) + convert(varchar(50,Column3),50)
END ASC

But clearer to use the 3 separate sorts.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-04-11 : 13:51:17

Jeff...

"But when you convert a datetime column to varchar then sort on it, it doesn't sort it correctly (well it sorts it correctly for a varchar column but I want it to sort it like it was still a datetime column). "

Tara's comment (above) seemed to imply a 2nd problem when converting them to varchar...I understood this to be because the data was displaying as mm/dd/yyyy...which you, me and others see as a date...but won't necessarily sort like a date in yyyymmdd format would...

I stand corrected if I misread the problem.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-11 : 13:56:03

Well I wasn't asking why it wasn't sorting correctly because I already understood why, I just wanted a workaround for it.

Tara

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-04-12 : 09:00:18

quote:
Rob -- I missed the difference between ours ... does mine have a problem with leading zeroes or dates ??

No, it doesn't, I was referring to Tara's CONVERT(varchar) option.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-12 : 11:55:25

Ohhh, didn't see her question .. gotcha! Thanks!

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-13 : 02:50:59

Thanks for the help, especially to Jeff and robvolk. We've been using dynamic sql for these types of things and have suffered due to performance issues with it. I am not the one writing these queries, so I have been helping them rewrite their queries. Hopefully we'll be able to remove most of the dynamic sql.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources