?
Solved

select as a param to datediff

Posted on 2006-07-11
5
Medium Priority
?
690 Views
Last Modified: 2008-01-09
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
Comment
Question by:aquasw
4 Comments
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17082696
Set Rowcount 1
select datediff(year,database..table1.datetime1,getdate()) from database..table1 order by database..table.datetime2
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 17086992
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
 
LVL 19

Accepted Solution

by:
grant300 earned 1500 total points
ID: 17087154
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
 
LVL 6

Expert Comment

by:ChrisKing
ID: 17162208
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

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Washington based MSP turned to OnPage to solve their needs for after-hours alerting when customers’ technologies failed. In this post  see how DNS benefited from rolling out OnPage as a solution: -Preserve their SLAs -Improve response time by…
In this age of digitization where the online market is increasingly becoming competitive each day, I’ll give you the truth bomb: simply putting your business out there is not enough. Sure, you’ve got impressive content and interesting graphic design.
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Is your organization moving toward a cloud and mobile-first environment? In this transition, your IT department will encounter many challenges, such as navigating how to: Deploy new applications and services to a growing team Accommodate employee…
Suggested Courses

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question