Solved

select as a param to datediff

Posted on 2006-07-11
5
629 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
5 Comments
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Marketing can be an uncomfortable undertaking, especially if your material is technology based. Luckily, we’ve compiled some simple and (relatively) painless tips to put an end to your trepidation and start your path to success.
In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now