Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TSQL Q from 461 book DATEDIFF Expand / Collapse
Author
Message
Posted Friday, January 10, 2014 9:48 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, April 20, 2014 8:11 PM
Points: 63, Visits: 187
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 @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 DATE
DECLARE @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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 12,034, Visits: 11,062
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 DATE
DECLARE @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 DATE
DECLARE @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

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, April 20, 2014 8:11 PM
Points: 63, Visits: 187
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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:47 PM
Points: 2,788, Visits: 5,972
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 Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1529894
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse