[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Informix SQL get age from ddmmyyyy field

Posted on 2011-02-22
4
Medium Priority
?
2,244 Views
Last Modified: 2012-05-11
The code below is not accurate as it only minuses the year

year(today)-year(DOB) as AGE

does anybody know a more accurate way of returing a persons age taking into account the day and month of the year as well?


0
Comment
Question by:JCTDD
[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
  • 2
  • 2
4 Comments
 
LVL 7

Expert Comment

by:jhp333
ID: 34956558
1. Simple, but approximate solution:
select ((today - DOB) / 365.25)::int

2. Accurate one:
select year(today) - year(DOB) - case when mdy(month(DOB),day(DOB),year(today)) > today then 1 else 0 end
0
 

Author Comment

by:JCTDD
ID: 35014658
running 2. Accurate one: in aqua studio I get error:

"Invalid day in date"

select year(today) - year(mailing_code) - case when mdy(month(mailing_code),day(mailing_code),year(today)) > today then 1 else 0 end
from table_1

Open in new window


do you know what is wrong?
0
 

Author Comment

by:JCTDD
ID: 35014663
example DOBs from the mailing_code field:
mailing_code
26/03/1941
1/10/1958
21/07/1961
0
 
LVL 7

Accepted Solution

by:
jhp333 earned 2000 total points
ID: 35014860
You need to set an environment variable, DBDATE.
It seems your date format requires DBDATE="DMY4/"
Actual syntax for setting environment variable depends on the OS.
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

650 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