How to use Alias in where cause in sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Dear All

Here my prob is how to use alias with this query....

Query:

SQL

SELECT convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, '00:00', LatenessN)), 0),  108) as Short from Daily where
EmpCodeC='003' and ClockDateD between ( '01/Jan/2015 12:00:00 AM') and ('31/Jan/2015 12:00:00 AM')

here i want to check Short <='02:00:00'

how to do this...

Posted 22-Jan-15 0:29am

prasanna.raj

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RAHUL(10217975) · Answer 1 · 2015-01-22T00:31:00

Solution 1

SQL

Select * from 
(
SELECT convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, '00:00', LatenessN)), 0),  108) as Short from Daily where
EmpCodeC='003' and ClockDateD between ( '01/Jan/2015 12:00:00 AM') and ('31/Jan/2015 12:00:00 AM')
) T -- Not 'Table'
Where Short <='02:00:00'

Posted 22-Jan-15 0:31am

RAHUL(10217975)

Updated 22-Jan-15 0:58am

v2

Comments

CHill60 22-Jan-15 6:42am

Incorrect syntax near the keyword 'Table'.
This is because you have used the reserved word "Table" as the alias - change it to something else or (not advised) surround it with square brackets [Table]

Zoltán Zörgő 22-Jan-15 6:46am

Table is a reserved word. You can use it but only like this:
) as [Table]
In this case it is easier to use an other alias for the subquery. Like t for example.