asked on

SQL SORT CONVERT(VARCHAR, GETDATE(), 101) by Date and NOT Text

Format date and then sort by datevalue.

I am joining three tables and formatting the dates. I receive error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified." if a column in ORDER BY is NOT found in the SELECT statement. Hence sorting by CreatedDate and NOT SO.dateCreation. The problem is that the sort is performed by 01/xx/2017 and 01/xx/2018. I want the sort to be performed by date and not text.

SELECT DISTINCT
	SO.Name AS 'SalesOrder', 
	CONVERT(VARCHAR, SO.dateCreation, 101) AS 'CreatedDate',
        SO.F1,
        SO.F2,
FROM SalesOrder SO
ORDER BY CreatedDate DESC

Open in new window

Jim Horn

>The problem is that the sort is performed by 01/xx/2017 and 01/xx/2018. I want the sort to be performed by date and not text.
That's because you've converted it to a varchar value, which means any ORDER BY will be text sorting and not date sorting.

So to solve your problem..

SO.dateCreation should remain (or be converted to) a date, or

Add another column for SO.dateCreation as a date value, sort on that, but only display the varchar value.

Russ Suter

Leaving aside for the moment that you should let the business logic layer, not the database, perform the date to text conversion.

The issue here is that anything that appears in the ORDER BY clause must be included in the SELECT clause. You could do the following:

SELECT DISTINCT
	SO.Name AS 'SalesOrder', 
	CONVERT(VARCHAR, SO.dateCreation, 101) AS 'CreatedDate',
        SO.F1,
        SO.F2,
        CreatedDate
FROM SalesOrder SO
ORDER BY CreatedDate DESC

Open in new window

This will return an additional column but you can easily ignore that column in whatever code is consuming the query result.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CipherIS

ASKER

@Jim. I'm aware of why the sort is occurring the way it is. I do not want times to be displayed hence the conversion.

CipherIS

ASKER

@Russ - don't want the column to appear twice hence the question on how to solve.

Russ Suter

@CipherIS - In that case and if you insist on implementing business logic in the database layer (which is bad practice), Scott Pletcher's first suggestion using a subquery would be what you need.

CipherIS

ASKER

@Scott. Thanks. Used the subquery.

SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

Bummer... Too late.... Should have refreshed before submitting

Still, have a look at my post above and it might give you some extra options....

Cheers,
Mark Wills

Scott Pletcher

Bummer... Too late.... Should have refreshed before submitting

So you wouldn't have offered them additional options you already knew about if you didn't think you would get points? Bummer :-).

Mark Wills

>> So you wouldn't have offered them additional options you already knew about if you didn't think you would get points? Bummer :-).

Nope, not at all like that. Sounds bad though, doesnt it.....

More like, Scott is always very good, and my slow typing beats me yet again.

Which doesnt sound much better, because I post in this place for two reasons, and yes, points are one of them. More importantly, is to help people. So, I will (often enough) post after the event....

The more important sentence out of that post was "Still, have a look at my post above and it might give you some extra options...."

To be perfectly honest, I dont really enjoy the "digs" or "taunts" and often worse, that seems inevitable when I post in a thread where you have already posted.

And even endorsed your post. (despite " Name AS 'SalesOrder', " in the outer select,)

CipherIS

ASKER

Thanks Mark

Mark Wills

My pleasure, and Thank you :)