CODEWITHSUNDEEP
sql-servert-sqldatedatediff
Anne
Anne

Reputation: 21

Using table values in DATEDIFF function

So I am learning about TSQL and have been given an assignment to do an App that details stuff about a certain series of movies. One of the things I would like to add to the finished product is a DATEDIFF where I have the birthdate of the main actor and the Premiere date of the movie but would like to return the age of the actor at the time of the Premiere.

My question is, HOW do I specify the dates, given a DATEDIFF statement if those dates already exist in a table?

Examples of the tables follow.

Bond

BondID   FirstName   LastName   Birthdate
1        Sean        Connery    8/25/1930
2        George      Lazenby    9/5/1939
3        Roger       Moore      10/14/1927
4        Timothy     Dalton     3/21/1946
5        Pierce      Brosnan    5/16/1953
6        Daniel      Craig      3/2/1968

Film

ID   BondID    FilmTitle             Premiere
1      1       Dr. No                1963-05-08
2      1       From Russia With Love 1974-04-08
3      1       Goldfinger            1964-12-22
4      1       Thunderball           1965-12-29

Here is what I have presently.

SELECT b.Birthdate, f.Premiere, b.FirstName, b.LastName, f.FilmTitle
FROM Bond b INNER JOIN FilmID  f ON b.BondID = f.BondID
WHERE b.BondID = 1

SELECT DATEDIFF(YEAR, GETDATE(), GETDATE())  AS "YearDif"
WHERE GETDATE() = Bond.Birthdate AND GETDATE() = f.Premiere

Data

     Birthdate  Premiere    FirstName LastName  FilmTitle
1    1930-08-25 1963-05-08  Sean      Connery   Dr. No
2    1930-08-25 1974-04-08  Sean      Connery   From Russia With Love
3    1930-08-25 1964-12-22  Sean      Connery   Goldfinger
4    1930-08-25 1965-12-29  Sean      Connery   Thunderball

I would like to add a column that states the age of the actor at the time of the premiere...I think I have a handle on that one... But...

I get that the second part of the DATEDIFF part isn't correct, but how do I tell the DATEDIFF where to get the date values?

Any help would be wonderous!

Upvotes: 1

Views: 1642

Answers (2)

Fuzzy
Fuzzy

Reputation: 3810

Your query:

SELECT DATEDIFF(YEAR, GETDATE(), GETDATE())  AS "YearDif"
WHERE GETDATE() = Bond.Birthdate AND GETDATE() = f.Premiere

will not work for what you want. GETDATE() will only return today's date so in essence the above will only return a row if the birthdate and the premiere were today at the very moment you ran the query.

What you need to do is is the QUERY section below:

SAMPLE DATA:

IF OBJECT_ID('tempdb..#Bond') IS NOT NULL
DROP TABLE #Bond
CREATE TABLE #Bond
(BondID INT ,FirstName VARCHAR(20),LastName VARCHAR(20),Birthdate  DATE)

INSERT INTO #Bond
VALUES
 (1        ,'Sean'        ,'Connery'    ,'8/25/1930')
,(2        ,'George'      ,'Lazenby'    ,'9/5/1939')
,(3        ,'Roger'       ,'Moore'      ,'10/14/1927')
,(4        ,'Timothy'     ,'Dalton'     ,'3/21/1946')
,(5        ,'Pierce'      ,'Brosnan'    ,'5/16/1953')
,(6        ,'Daniel'      ,'Craig'      ,'3/2/1968')

IF OBJECT_ID('tempdb..#Film') IS NOT NULL
DROP TABLE #Film

CREATE TABLE #Film
(ID  INT , BondID  INT , FilmTitle       VARCHAR(50),      Premiere  DATE)
INSERT INTO #Film
VALUES (1    ,  1,'Dr. No'              ,'1963-05-08')
,(2    ,  1,'From Russia With Love','1974-04-08')
,(3    ,  1,'Goldfinger'           ,'1964-12-22')
,(4    ,  1,'Thunderball'          ,'1965-12-29')

QUERY:

    SELECT b.Birthdate, f.Premiere, b.FirstName, b.LastName, f.FilmTitle , DATEDIFF(YEAR, b.Birthdate, Premiere) AS AgeOfActor
FROM #Bond b INNER JOIN #Film  f ON b.BondID = f.BondID

RESULT:

enter image description here

RESPONSE TO COMMENT:

you can always count the days and then devide by 365:

SELECT b.Birthdate, f.Premiere, b.FirstName, b.LastName, f.FilmTitle , DATEDIFF(day, b.Birthdate, Premiere)/365 AS AgeOfActor
FROM #Bond b INNER JOIN #Film  f ON b.BondID = f.BondID

enter image description here

or for more accuracy you can do it in seconds :P

SELECT b.Birthdate, f.Premiere, b.FirstName, b.LastName, f.FilmTitle ,  DATEDIFF(second, b.Birthdate, Premiere)/(365.25*24*60*60)   AS AgeOfActor
FROM #Bond b INNER JOIN #Film  f ON b.BondID = f.BondID

enter image description here

Upvotes: 1

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

Such things can be done in SQL in just one step. There is no need to store the intermediate results anywhere to pass them later on.

You just need to pass your column values from the table into the function call like that:

SELECT 
 b.Birthdate, f.Premiere, b.FirstName, b.LastName, f.FilmTitle,
 DATEDIFF(YEAR, b.Birthdate, f.Premiere) AS actor_age_at_premiere
FROM 
 Bond b 
 INNER JOIN FilmID f 
  ON b.BondID = f.BondID

Assuming that an actor was born before the premiere date of a film he/she played in (which is a fair assumption) the result will be a positive Integer.

If you'd like to include the actor age as of now, then your third argument would be GETDATE() instead of Premiere column:

DATEDIFF(Year, b.Birthdate, GETDATE()) AS actor_age_current

Upvotes: 0

Related Questions