Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

select as a param to datediff

Posted on 2006-07-11
5
Medium Priority
?
681 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

If you’re involved with your company’s wide area network (WAN), you’ve probably heard about SD-WANs. They’re the “boy wonder” of networking, ostensibly allowing companies to replace expensive MPLS lines with low-cost Internet access. But, are they …
While opting for any web-to-print solution, you need to discuss with your team and some of your end users and know their opinions about your decisions. In this article we list down some questions you need to ask yourself.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

705 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