• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 690
  • Last Modified:

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
0
aquasw
Asked:
aquasw
1 Solution
 
sathyagiriCommented:
Set Rowcount 1
select datediff(year,database..table1.datetime1,getdate()) from database..table1 order by database..table.datetime2
0
 
Joe WoodhousePrincipal ConsultantCommented:
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
0
 
grant300Commented:
I get the impression that you have several rows in the table for for each DATETIME2 value and you want the datediff between the most recent DATETIME1 and GETDATE for each DATETIME2.  Is that correct?

Let's break that down.

SELECT DATEDIFF(YEAR, MAX(DATETIME1), GETDATE()), DATETIME2
  FROM TABLE1
GROUP BY DATETIME2
ORDER BY DATETIME2

You can remove the DATETIME2 field from the select list if you wish but I suspect the query result is kind of worthless without it.

Bill
0
 
ChrisKingCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now