CODEWITHSUNDEEP
sql
tonykurishinkal
tonykurishinkal

Reputation: 11

SELECT colum containing values >1000

I am using a SQL query

select 
    datediff(hour,Convert(DateTime,Convert(DateTime, Convert(nVarChar, cast(complaintdate as datetime), 101)) + '' +  
    Convert(varchar,complainttime,114)),dateadd(mi,330,GETUTCDATE())) 
from complaintregister

It creates a column like

No column name
  15455
  15259
  15190
  15190
  15165
   300
   200
  1000
   500

and I want a column containg values > 1000

Upvotes: 1

Views: 168

Answers (2)

Ivo
Ivo

Reputation: 3436

If it is SQL server, you could dot something like this

    select tablename.colname from 
(select datediff(hour,Convert(DateTime,Convert(DateTime, Convert(nVarChar, cast(complaintdate as datetime), 101)) +''+  Convert(varchar,complainttime,114)),dateadd(mi,330,GETUTCDATE())) as colname
from complaintregister) as tablename
where tablename.colname > 1000

Don't have a test db atm, so you have to play around with it a bit

Upvotes: 0

Guffa
Guffa

Reputation: 700322

Just repeat the expression in the condition:

select datediff(hour,Convert(DateTime,Convert(DateTime, Convert(nVarChar, cast(complaintdate as datetime), 101)) +''+  Convert(varchar,complainttime,114)),dateadd(mi,330,GETUTCDATE()))
from complaintregister
where datediff(hour,Convert(DateTime,Convert(DateTime, Convert(nVarChar, cast(complaintdate as datetime), 101)) +''+  Convert(varchar,complainttime,114)),dateadd(mi,330,GETUTCDATE())) > 1000

The query planner should recognise that the expressions are identical and actually only calcuate it once for each record.

Alternatively you can use a nested query:

select time
from (
  select datediff(hour,Convert(DateTime,Convert(DateTime, Convert(nVarChar, cast(complaintdate as datetime), 101)) +''+  Convert(varchar,complainttime,114)),dateadd(mi,330,GETUTCDATE())) as time
  from complaintregister
) x
where time > 1000

Upvotes: 5

Related Questions