Visual Studio .NET - select distinct and CAST after CONVERT problem

Asked By Andy Kalbvleesch on 19-Aug-06 07:56 AM
--
-- Definition for stored procedure getprijzen1 : 
--

CREATE PROCEDURE dbo.getprijzen1
(
@reisID int
)
AS
BEGIN
SELECT DISTINCT vertrekdagen.vertrekdagID,
CONVERT(CHAR(10),  vertrekdagen.datum, 105) AS datum, vertrekdagen.duur
FROM vertrekdagen
WHERE reisID = @reisID and datum >  {fn NOW()}
order by CAST(datum AS DATETIME) asc
END


Throws an error: 
ORDER BY items must appear in the select list if SELECT DISTINCT is specified

Anybody a clue ?

Order By - Asked By Roberto Chieregato on 19-Aug-06 10:42 AM

in order by use vertrekdagen.datun instead of CAST(datum as Datetime):

SELECT DISTINCT vertrekdagen.vertrekdagID,
CONVERT(CHAR(10), vertrekdagen.datum, 105) AS datum, vertrekdagen.duur
FROM vertrekdagen
WHERE reisID = @reisID and datum > {fn NOW()}
order by vertrekdagen.datum asc

Order by (2) - Asked By Roberto Chieregato on 19-Aug-06 10:50 AM

..... obviously if your column vertrekdagen.datum is already of datetype DateTime, otherwise you can add a column to the select...

SELECT DISTINCT vertrekdagen.vertrekdagID,
CONVERT(CHAR(10), vertrekdagen.datum, 105) AS datum, vertrekdagen.duur,
CAST(datum AS DATETIME) AS Dummy_column
FROM vertrekdagen
WHERE reisID = @reisID and datum > {fn NOW()}
order by Dummy_column asc

hmm second works - Asked By Andy Kalbvleesch on 19-Aug-06 12:24 PM

The first still gave me an error while the second version with a dummy column works like a charm. Thnx.