SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TSQL Q from 461 book DATEDIFF


TSQL Q from 461 book DATEDIFF

Author
Message
rho_pooka
rho_pooka
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 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 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'


Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65464 Visits: 17980
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 Modens 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)
rho_pooka
rho_pooka
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 205
Thank you, I haven't drilled into dynamic sql yet… will revisit this once I do.

-Ben
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43577 Visits: 19863
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search