SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 subtract 2 columns from 2 different tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jn
Starting Member

United Kingdom
3 Posts

Posted - 04/29/2013 :  02:45:24  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 04/29/2013 :  02:49:23  Show Profile  Reply with Quote
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
Go to Top of Page

jn
Starting Member

United Kingdom
3 Posts

Posted - 04/29/2013 :  03:17:18  Show Profile  Reply with Quote
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
Go to Top of Page

jn
Starting Member

United Kingdom
3 Posts

Posted - 04/29/2013 :  03:19:12  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/29/2013 :  04:01:04  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000