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: Sunday, April 13, 2014 7:32 PM Points: 63, Visits: 186
 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
 SSChampion Group: General Forum Members Last Login: Yesterday @ 3:44 PM Points: 11,970, Visits: 10,995
 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: Sunday, April 13, 2014 7:32 PM Points: 63, Visits: 186
 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 Group: General Forum Members Last Login: Yesterday @ 10:00 PM Points: 2,763, Visits: 5,905
 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.I am a great believer in luck, and I find the harder I work the more I have of it. Stephen LeacockForum Etiquette: How to post data/code on a forum to get the best help
Post #1529894

 Permissions