Problem with DATEDIFF

Question

1.00/5 (3 votes)

See more:

This my script :

CASE
WHEN Users.ResignationDate IS NOT NULL THEN
DATEDIFF(YEAR,WorkStartDate,ResignationDate)
ELSE
DATEDIFF(YEAR,WorkStartDate,GETDate())END as AncinitetViaÅr,

Its give me result like this: 2

But i wanna my result look like this : 2 Years

Can someone please help or point me in the right direction. Thanks.

What I have tried:

CASE
WHEN Users.ResignationDate IS NOT NULL THEN
DATEDIFF(YEAR,WorkStartDate,ResignationDate)
ELSE
DATEDIFF(YEAR,WorkStartDate,GETDate()END as AncinitetViaÅr)+ 'Years'

But its not working

Posted 23-Feb-17 2:14am

Man Down

Updated 23-Feb-17 3:15am

Add a Solution

2 solutions

Solution 1

Try google search for a change, the answer will be there.. sql format datetime to string[^]

Posted 23-Feb-17 2:21am

Graeme_Grant

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bryian Tan · Accepted Answer · 2017-02-23T03:15:00

Solution 2

I think you're on the right track, the query just need a little tweet. The main thing are

1. Convert the integer return from DATEDIFF to string
2. Append + ' Years' string at the end of each condition

SQL

CASE
 WHEN Users.ResignationDate IS NOT NULL THEN
  CONVERT(VARCHAR(3),DATEDIFF(YEAR,WorkStartDate,ResignationDate))+ ' Years'
 ELSE
    CONVERT(VARCHAR(3),DATEDIFF(YEAR,WorkStartDate,GETDate())) + ' Years'  
END as AncinitetViaÅr

Posted 23-Feb-17 3:15am

Bryian Tan

Comments

Man Down 24-Feb-17 3:00am

tnx for your help. i got one more issue with month , i use this for month and then when i look at result is look like this 2 years 556 month 22 day : CONVERT(varchar(4), DATEDIFF(YEAR, WorkStartDate, GETDATE())) + ' years '+
CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy,GETDATE(),WorkStartDate), WorkStartDate), GETDATE()) AS varchar(4)) +' month '+
CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, WorkStartDate, GETDATE()), WorkStartDate), GETDATE()), DATEADD(yy, DATEDIFF(yy, WorkStartDate, GETDATE()), WorkStartDate)), GETDATE()) AS varchar(2)) +' day' end) as result
____________________________________
When i change postion WorkStartDate and GetDate() , i got another strang result ,something like this : 2 years -2 month 22 day

Bryian Tan 24-Feb-17 9:57am

hmmm, not sure, I think that could be simplify, here is an example

DECLARE @WorkStartDate Date
SET @WorkStartDate = '9/11/2014'
SELECT
CONVERT(varchar(4), DATEDIFF(dd, @WorkStartDate, GETDATE())/365) + ' years '+

CONVERT(varchar(4), DATEDIFF(MONTH, @WorkStartDate, GETDATE()) % 12) + ' month '+

CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, @WorkStartDate, GETDATE()), @WorkStartDate), GETDATE()),
DATEADD(yy, DATEDIFF(yy, @WorkStartDate, GETDATE()), @WorkStartDate)), GETDATE()) AS varchar(2)) +' day'

You can compare the result here: Calculate Duration Between Two Dates – Results[^]

Man Down 24-Feb-17 10:03am

thank you so much Bryian