asked on

select as a param to datediff

Hi.
I am using datediff command.
For the second argument I am giving a select command.
It is not working.
What is the problem?

* I have a table with 2 datetime columns: DATETIME1, DATETIME2
* this is the command I am trying to run:

select DATEDIFF
(
YEAR,
select top 1 DATABASE..TABLE1.DATETIME1 from DATABASE..TABLE1 order by DATABASE..TABLE1.DATETIME2 ,
getdate()
)

Thanks in advance

sathyagiri

Set Rowcount 1
select datediff(year,database..table1.datetime1,getdate()) from database..table1 order by database..table.datetime2

Joe Woodhouse

You have to be running a recent version of ASE for "top 1" to work. Which version are you on?

It might work if you wrap the entire SELECT in parentheses:

select DATEDIFF
(
YEAR,
(select top 1 DATETIME1 from DATABASE..TABLE1 order by DATETIME2) ,
getdate()
)

If that doesn't work, just use a local variable:

declare @topdate datetime
select @topdate = max(DATETIME1) from DATABASE..TABLE1 order by DATETIME2

select datediff(year, @topdate, getdate())
go

ASKER CERTIFIED SOLUTION

grant300

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ChrisKing

sathyagiri has the cleanest conversion (depending on the version you are running, you can also keep the "TOP 1")

select top 1 DATEDIFF(YEAR, DATABASE..TABLE1.DATETIME1, getdate()) from DATABASE..TABLE1 order by DATABASE..TABLE1.DATETIME2

and the problem ?
You can't call a sub query as a parameter of a function