September 27, 2006 at 12:12 pm
I have a union query that has two aliases vDay based upon :
SELECT convert(varchar, datepart(dd, s.dtDate)) 'vDay' .... FROM Schedule as S
UNION
SELECT convert(varchar, datepart(dd, h.dtDate)) 'vDay' .... FROM Holidays as H
However, I am trying to Order by vDay (but as an INT)... I tried ORDER BY CAST(vDay as INT) with no avail. The ASP frontend needs vDay to be a varchar, but I need to order the recordset.
September 27, 2006 at 12:35 pm
Did you try
SELECT t.vDay FROM (SELECT CONVERT(VARCHAR, DATEPART(dd, s.dtDate)) 'vDay' .... FROM Schedule AS S
UNION
SELECT CONVERT(VARCHAR, DATEPART(dd, h.dtDate)) 'vDay' .... FROM Holidays AS H
ORDER BY vday
Francis
September 27, 2006 at 1:05 pm
Try This:
Select tableName.Col1,tableName.Col2,tableName.Col3,... from (
SELECT convert(varchar, datepart(dd, s.dtDate)) 'vDay' ....,datepart(dd, s.dtDate) as Dy FROM Schedule as S
UNION
SELECT convert(varchar, datepart(dd, h.dtDate)) 'vDay' ....,datepart(dd, H.dtDate) as Dy FROM Holidays as H) tableName
Order by Dy
Thanks
Sreejith
September 27, 2006 at 1:32 pm
Since the column is varchar, it would order things like 1,11,12,13,14,2,21,22,25,3,30,31
September 27, 2006 at 1:34 pm
I will try out the subquery T-SQL
September 27, 2006 at 2:03 pm
ok then how about
SELECT t.vDay ... FROM (SELECT CONVERT(VARCHAR, DATEPART(dd, s.dtDate)) 'vDay' ,CONVERT(int, DATEPART(dd, s.dtDate)) 'vDayorderby' .... FROM Schedule AS S
UNION
SELECT CONVERT(VARCHAR, DATEPART(dd, h.dtDate)) 'vDay' ,CONVERT(int, DATEPART(dd, s.dtDate)) 'vDayorderby' .... FROM Holidays AS H
ORDER BY vDayorderby
Francis
September 27, 2006 at 4:38 pm
DATEPART returns int (see BOL)
So, there is no point in using
CONVERT(int, DATEPART(dd, s.dtDate))
_____________
Code for TallyGenerator
September 28, 2006 at 10:15 am
You might try something like this from your original Query in lieu of the subquery to sort by:
SELECT Case when DATEPART(dd,dtDate) < 10 then '0' + CONVERT(VARCHAR(1),DATEPART(dd, dtDate)) else Convert(Varchar(2),DATEPART(dd, dtDate))end as 'vDay' FROM Schedule
which will give you the Varchar(2) of the Day to sort by.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply