year ,month ,f i am giving date 02-01-2022 to 25-02-2024 how two sday

Question

if i am giving date

from date :02-02-2023

to date :25-04-2024

show completed year,balance months and days

result will be 1 year 02-02-2023 -02-02-2024

month will be 2 months 02-02-2024 - 02-04-2024

days wiil be 23 days 02-04-2024-25-04-2024

how to write sql query to get result will in 1 year,2 months and 23 days

Naimish Makwana · Answer

You can use the DATEDIFF function in SQL to calculate the difference between two dates. However, SQL does not directly provide a way to format the output as “1 year, 2 months, 23 days”. You would need to calculate the years, months, and days separately and then concatenate the results. Here is an example of how you might do this: DECLARE @FromDate DATE = '2023-02-02', @ToDate DATE = '2024-04-25' SELECT CAST(DATEDIFF(YEAR, @FromDate, @ToDate) AS VARCHAR) + ' years, ' + CAST(DATEDIFF(MONTH, DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate), @FromDate), @ToDate) AS VARCHAR) + ' months, ' + CAST(DATEDIFF(DAY, DATEADD(MONTH, DATEDIFF(MONTH, DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate), @FromDate), @ToDate), @FromDate), @ToDate) AS VARCHAR) + ' days' This query first calculates the difference in years, then subtracts that many years from the end date and calculates the difference in months, and finally subtracts that many months from the end date and calculates the difference in days. The results are then concatenated into a string. Thanks