SQLTeam.com | Weblogs | Forums

Order by operator


#1

new to SQL ::slight_smile:
In the simple query below I was able to successful obtain data results. in the second query- it kept giving incorrect column name as an error. question is- if it returned data in the first query using the same operator, why didn't it work it the second? basically- we are trying to see staff who log in > 6:00pm
thank you for any help given.

select Log_ID, WFNJ_No, EOC_No, Note, Date_Created, Right(Convert(Varchar,Date_Created,100),7) as LogginTime,UserId_Created, XML_Data
from Caselog
Where Date_Created >= '2016-01-01 00:00:00.000'
Order by Date_Created, LogginTime

Select Username,LastActivityDate,RIGHT(CONVERT(VARCHAR, LastActivityDate, 100),7) as LogginTime
From aspnet_Users_Activity
order by LogginTime


#2

what is the column name ? Does it exists in the aspnet_Users_Activity table ?


#3

In that case :slight_smile: I recommend that you only EVER use:

where you have a SIZE definition to VARCHAR e.g.

Convert(Varchar(19),Date_Created,100)

The default size for VARCHAR, if you don't provide an explicit size, can catch you out.

There are several places in SQL where this sort of "flexibility" exists, for all sorts of legacy/historical reasons, and I wish that ALL of them were only available if you set an "I need the legacy method" flag.