Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 TSQL Q from 461 book DATEDIFF Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, January 10, 2014 9:48 AM
 Valued Member Group: General Forum Members Last Login: Wednesday, February 18, 2015 7:20 AM Points: 71, Visits: 205
 Greetings, another question for the group. I've been writing out my own homework exercises as I work through the 461 training kit; one of the problems I posed for myself was the following:Find difference in years, then months, then days between youngest employee and oldest employee… create column name “Age Gap”, value should be read: “diff in (years), diff in (months), diff in (days)”So here is the code I wrote out, then my question at the bottom`DECLARE @youngest AS DATEDECLARE @oldest AS DATE SET @youngest = (SELECT TOP (1) birthdate FROM hr.employees ORDER BY birthdate DESC) SET @oldest = (SELECT TOP (1) birthdate FROM hr.employees ORDER BY birthdate ASC)SELECT CONCAT('The diff in years ', (DATEDIFF(month, @oldest, @youngest))) AS'Age Gap'`My question is first, is there an easier way of doing this, secondly how come when I add another variable "@interval" and add it to the DATEDIFF statement it does not work?`DECLARE @youngest AS DATEDECLARE @oldest AS DATE DECLARE @interval AS VARCHAR (10)SET @interval = 'month' SET @youngest = (SELECT TOP (1) birthdate FROM hr.employees ORDER BY birthdate DESC) SET @oldest = (SELECT TOP (1) birthdate FROM hr.employees ORDER BY birthdate ASC)SELECT CONCAT('The diff in ', @interval, ' ', (DATEDIFF(@interval, @oldest, @youngest))) AS'Age Gap'`
Post #1529875
 Posted Friday, January 10, 2014 10:06 AM
 SSCoach Group: General Forum Members Last Login: 2 days ago @ 8:08 PM Points: 16,145, Visits: 16,850
 rho_pooka (1/10/2014)Greetings, another question for the group. I've been writing out my own homework exercises as I work through the 461 training kit; one of the problems I posed for myself was the following:Find difference in years, then months, then days between youngest employee and oldest employee… create column name “Age Gap”, value should be read: “diff in (years), diff in (months), diff in (days)”So here is the code I wrote out, then my question at the bottom`DECLARE @youngest AS DATEDECLARE @oldest AS DATE SET @youngest = (SELECT TOP (1) birthdate FROM hr.employees ORDER BY birthdate DESC) SET @oldest = (SELECT TOP (1) birthdate FROM hr.employees ORDER BY birthdate ASC)SELECT CONCAT('The diff in years ', (DATEDIFF(month, @oldest, @youngest))) AS'Age Gap'`My question is first, is there an easier way of doing this, secondly how come when I add another variable "@interval" and add it to the DATEDIFF statement it does not work?`DECLARE @youngest AS DATEDECLARE @oldest AS DATE DECLARE @interval AS VARCHAR (10)SET @interval = 'month' SET @youngest = (SELECT TOP (1) birthdate FROM hr.employees ORDER BY birthdate DESC) SET @oldest = (SELECT TOP (1) birthdate FROM hr.employees ORDER BY birthdate ASC)SELECT CONCAT('The diff in ', @interval, ' ', (DATEDIFF(@interval, @oldest, @youngest))) AS'Age Gap'`Your DATEDIFF is failing because @interval is a varchar. The first argument is the datepart that you want to use to calculate the boundaries. It is NOT a string. To do this the way you are trying you would have to use dynamic sql.http://technet.microsoft.com/en-us/library/ms189794.aspx _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1529885
 Posted Friday, January 10, 2014 10:13 AM
 Valued Member Group: General Forum Members Last Login: Wednesday, February 18, 2015 7:20 AM Points: 71, Visits: 205
 Thank you, I haven't drilled into dynamic sql yet… will revisit this once I do.-Ben
Post #1529889
 Posted Friday, January 10, 2014 10:22 AM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 9:48 AM Points: 8,222, Visits: 17,788
 There's an easier way to do this without using variables.`SELECT 'The diff in years ' + DATEDIFF(YEAR, MIN(birthdate), MAX(birthdate))FROM hr.employees`As Sean pointed out, you can't use a variable for your interval as it's not a string. Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2
Post #1529894

 Permissions