subtract 2 columns from 2 different tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

subtract 2 columns from 2 different tables

Author

Topic

jn
Starting Member

3 Posts

Posted - 2013-04-29 : 02:45:24

Hi

I have 2 columns from 2 different tables.
I need to find the age of a group of people from 2 tables.

From 1 table, I can get the date of transaction that is in
the format 1975-01-01 ie YYYY-MM-DD

From the other table, is the year of birth for example 1967.

The fields are in different format.How do I get the age?

JJ

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 02:49:23

using the given info, you can only do this


SELECT DATEDIFF(yy,col1,col2)
FROM..

but you need to relate tables using common field(s) before that.

Also if field containing year value is integer you need to cast it to varchar before you use it in DATEDIFF

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jn
Starting Member

3 Posts

Posted - 2013-04-29 : 03:17:18

I am using MySQL workbench and I am new to it.
How do I cast it to varchar?
journal where the date of transaction is and patient is the year of birth.
I tried running the queries:

Update journal
set journalsubset.date = journalsubset.date - patient.dob
from journal inner join patient
on
journalsubset.id = patientsubset.id

But it cannot work.

JJ

jn
Starting Member

3 Posts

Posted - 2013-04-29 : 03:19:12

How do I subtract the date of transaction(YY-mm-dd) from the age(YY)
both are from 2 different tables?

the id from journalsubset reference the patient table.

JJ

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 04:01:04

quote:
Originally posted by jn

I am using MySQL workbench and I am new to it.
How do I cast it to varchar?
journal where the date of transaction is and patient is the year of birth.
I tried running the queries:

Update journal
set journalsubset.date = journalsubset.date - patient.dob
from journal inner join patient
on
journalsubset.id = patientsubset.id

But it cannot work.

JJ

hmm...why are setting the result to date field itself?
your original requirement asked for age which will be an int value

Also you're using mySQL and this is ms sql server forum. Please post it in some MySQL forum for getting specific syntax help. Here we deal with transact sql so solutions may not work well in MySQL.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources