Date difference with total years, months and days
-
How can I obtain total number of years, months and days between 2 dates in a single sql query. eg : date1 = 2012-07-02 date2 = 2013-09-04 difference between date1 and date2 should show as "1 year, 2 months and 2 days" Datediff function returns difference only by years or months or days etc...how can i do that
-
How can I obtain total number of years, months and days between 2 dates in a single sql query. eg : date1 = 2012-07-02 date2 = 2013-09-04 difference between date1 and date2 should show as "1 year, 2 months and 2 days" Datediff function returns difference only by years or months or days etc...how can i do that
maxRazar wrote:
difference between date1 and date2 should show as "1 year, 2 months and 2 days"
That's usually not done at the SQL-level, but in code. The SQL-statement would return the difference (just subtract one from the other). It's a bad idea to format data in Sql.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
How can I obtain total number of years, months and days between 2 dates in a single sql query. eg : date1 = 2012-07-02 date2 = 2013-09-04 difference between date1 and date2 should show as "1 year, 2 months and 2 days" Datediff function returns difference only by years or months or days etc...how can i do that
I'm with Eddy on this one do your formatting in the UI. However did it occur to you to do the datediff 3 times getting the Y/M/D separately and concatenate them as you want.
Never underestimate the power of human stupidity RAH
-
I'm with Eddy on this one do your formatting in the UI. However did it occur to you to do the datediff 3 times getting the Y/M/D separately and concatenate them as you want.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
do the datediff 3 times getting the Y/M/D separately and concatenate them as you want.
That won't work. Consider:
declare @start date = '20121231';
declare @end date = '20130101';
select
cast(datediff(year, @start, @end) as varchar(5)) + ' year, '- cast(datediff(month, @start, @end) as varchar(5)) + ' month, '
- cast(datediff(day, @start, @end) as varchar(5)) + ' day'
;
The output will be
1 year, 1 month, 1 day, which is obviously wrong.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Mycroft Holmes wrote:
do the datediff 3 times getting the Y/M/D separately and concatenate them as you want.
That won't work. Consider:
declare @start date = '20121231';
declare @end date = '20130101';
select
cast(datediff(year, @start, @end) as varchar(5)) + ' year, '- cast(datediff(month, @start, @end) as varchar(5)) + ' month, '
- cast(datediff(day, @start, @end) as varchar(5)) + ' day'
;
The output will be
1 year, 1 month, 1 day, which is obviously wrong.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
As stated, the query you showed will provide the wrong results, however, that doesn't mean datediff 3 times can't be used. There is just more to it. Tim
-
How can I obtain total number of years, months and days between 2 dates in a single sql query. eg : date1 = 2012-07-02 date2 = 2013-09-04 difference between date1 and date2 should show as "1 year, 2 months and 2 days" Datediff function returns difference only by years or months or days etc...how can i do that
As other's have said, formatting shouldn't be done in SQL. However, if that is the requirment of the application, other replies do provide some insight. Post what you have and then it can be reviewed and commented on. Tim
-
Mycroft Holmes wrote:
do the datediff 3 times getting the Y/M/D separately and concatenate them as you want.
That won't work. Consider:
declare @start date = '20121231';
declare @end date = '20130101';
select
cast(datediff(year, @start, @end) as varchar(5)) + ' year, '- cast(datediff(month, @start, @end) as varchar(5)) + ' month, '
- cast(datediff(day, @start, @end) as varchar(5)) + ' day'
;
The output will be
1 year, 1 month, 1 day, which is obviously wrong.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
You are right, a simple datediff 3 times will not do it, however with the application of a little imagination and some work with SSMS I know you can do this, I have seen it done in an example somewhere but dammed if I am willing to chase it down.
Never underestimate the power of human stupidity RAH
