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: Monday, May 26, 2014 7:27 AM
Points: 71, Visits: 193
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: Yesterday @ 2:54 PM
Points: 13,471, Visits: 12,329
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: Monday, May 26, 2014 7:27 AM
Points: 71, Visits: 193
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


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:19 PM
Points: 3,660, Visits: 7,985
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.
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?

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