January 10, 2014 at 9:48 am
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 DATE
DECLARE @oldestAS 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 DATE
DECLARE @oldestAS 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'
January 10, 2014 at 10:06 am
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 @oldestAS 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 @oldestAS 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 10, 2014 at 10:13 am
Thank you, I haven't drilled into dynamic sql yet… will revisit this once I do.
-Ben
January 10, 2014 at 10:22 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply