1

I am trying to calculate age as of a given YYYYMMDD date (2013-12-13) using an unsigned integer YYYYMMDD date of birth variable (dob) from a Sybase IQ 15.4 database (setdata). It isn't my database and I have no control over how the data are stored. I am using the following code:

select 
convert(date,convert(varchar,dob)) as yearborn, 
DATE("2013-12-31") as present,
datediff(month,yearborn,present)/12 as age_in_yrs
from setdata

This works fine in the Interactive SQL tool I have, but when I run it using SAS connect with the same code (so I can get a log) I get this message:

ERROR: CLI execute error: [Sybase][ODBC Driver][Sybase IQ]Data exception - data type conversion is not possible. -- (dfe_Cast.cxx 835)

Update: SAS converts the commands into ANSI SQL. So I think I am looking for an ANSI way to do this, and that my function is not supported.

Mike Gardner
  • 6,611
  • 5
  • 24
  • 34
SarainVA
  • 11
  • 1
  • 3
  • You cannot refer to 'yearborn' this way, its a column alias in the current query and you cannot use it directly. You have to use a derived table (subquery) or a CTE if you want to do that. But why not just do it like this: – RobV Feb 11 '16 at 11:27
  • You cannot refer to 'yearborn' this way, its a column alias in the current query and you cannot use it directly. You have to use a derived table (subquery) or a CTE if you want to do that. The main challenge in your query is how to convert an integer with format YYYYMMDD to a datetime value, which is what you need for DATEDIFF(). The DATE() function may work for that, but you'll need to set the date_order option to 'YMD' . – RobV Feb 11 '16 at 11:33

0 Answers0