I am trying to build a query which will give me No of Years/Months/Days from Date. I have Date field (Date)and i have NoOfDays field (Numeric 10)(I am calculating No of Days from Date in NoOfDays field)

now i want result where i need to show No Of years/Months/years from selected date. For e.g. Date = 07/26/2015 the Result should be - Years=0,Months=3,Days=13 For e.g. Date = 08/20/2014 the Result should be - Years=1,Months=2,Days=19

i tried with several ways but could not able to get desired result.

asked 07 Nov '15, 09:34

ninad's gravatar image

ninad
21568
accept rate: 0%


I'm a bit confused what you really need. A Date doesn't have a number of years/months/days. Do you need the difference between the given date and today? If so, check out TIMESTAMPDIFF and EXTRACT functions.

link

answered 07 Nov '15, 12:39

Joachim%20Duerr's gravatar image

Joachim Duerr
8.7k1031133
accept rate: 18%

I need difference of Given Date and Today's date but in User Defined format i.e. Years=XX,Months=YY,Days=ZZ. (XX-no of years, YY-no of month, ZZ-no of days) User will add a date for e.g. "08/20/2014" i need to write a query which can give me output in this format - Years=1,Months=2,Days=19 whether ADS having readymade function? i tried with few queries but its not working as per expected...

(07 Nov '15, 14:49) ninad

my query is

SELECT "DATE",NUMDAYS, 'Years ='+ CAST(IIF(NUMDAYS>365,TIMESTAMPDIFF(SQL_TSI_YEAR,"DATE",convert(CURDATE(),SQL_DATE)),0) as SQL_CHAR) + 'Months ='+ CAST(IIF(NUMDAYS>30,IIF((NUMDAYS/30)>12,FLOOR(NUMDAYS/30)-12,FLOOR(NUMDAYS/30)),0) as SQL_CHAR) + 'Days ='+ CAST(IIF(NUMDAYS>30,MOD(CAST(NUMDAYS As SQL_INTEGER),30),NUMDAYS) as SQL_CHAR) AS Duration From XXX where XXX.ExamNo = 12345

(07 Nov '15, 14:49) ninad

there is no ready-made function because this is something wery specific. To get the distance between two date values in days, just substract one from the other: date1-date2. If you define a month always to be 30 days and a year always to be 365 days, then you can just calculate with that result.

(07 Nov '15, 20:21) Joachim Duerr
Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×172
×93
×17
×7

Asked: 07 Nov '15, 09:34

Seen: 2,672 times

Last updated: 07 Nov '15, 20:21

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.