Click here to monitor SSC
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
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
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)
rho_pooka
rho_pooka
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

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

-Ben
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8488 Visits: 18099
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