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
Solved

select as a param to datediff

Posted on 2006-07-11
5
654 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
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 500 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to add a function to sybase 10 1,239
ORCA Powerbuilder import 7 1,122
Sybase Customized sp_thresholdaction 2 681
Sybase 15.7 database backup 8 642
Giving access to ESXi shell console is always an issue for IT departments to other Teams, or Projects. We need to find a way so that teams can use ESXTOP for their POCs, or tests without giving them the access to ESXi host shell console with a root …
ConnectWise and their customers need to ensure critical alerts automatically reach the right person at the right time. MSP superheros efficiently respond to these alerts key is providing automatic, intelligent alerting that generates a complete audi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

792 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