# Thread: Number to dial day month year

1. Registered User
Join Date
Jun 2011
Posts
1

## Unanswered: Number to dial day month year

]Hi everyone,
How do I edit my view, this negative value.
is a negative value. How can I prevent this.

dbo.LH_001_PERSON.INDATE AS ISEGIRIS_TARIHI, DATEDIFF(DAY, dbo.LH_001_PERSON.INDATE, GETDATE()) AS KIDEM_GUNU, STR(DATEDIFF(DAY,
dbo.LH_001_PERSON.INDATE, GETDATE()) / 365) + ' YIL ' + STR(DATEDIFF(MONTH, dbo.LH_001_PERSON.INDATE, GETDATE()) - 12 * FLOOR(DATEDIFF(DAY,
dbo.LH_001_PERSON.INDATE, GETDATE()) / 365)) + ' AY ' + STR((DATEDIFF(DAY, dbo.LH_001_PERSON.INDATE, GETDATE()) - 365 * DATEDIFF(YEAR,
dbo.LH_001_PERSON.INDATE, GETDATE())) - 30 * (DATEDIFF(MONTH, dbo.LH_001_PERSON.INDATE, GETDATE()) - 12 * DATEDIFF(YEAR,
dbo.LH_001_PERSON.INDATE, GETDATE()))) + ' GUN' AS KIDEMI

Translate to english: Yil= Years, Ay= Month, Gün= Days

http://www.imagehosting.gr/di-LUPX.jpg

should be
9 YIL , 1 Ay, 28 Gün

2. Registered User
Join Date
Feb 2004
Posts
492
So you have a date and you want to know what the distance is to today expressed in years, months and days?

3. Registered User
Join Date
Feb 2004
Posts
492
If yes, I guess I would create a function that would do something like this:

Code:
```declare @adate as datetime

declare @years as int
, @months as int
, @days as int

select @years, @months, @days```

4. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369
Originally Posted by Kaiowas
If yes, I guess I would create a function that would do something like this:

Code:
```declare @adate as datetime

declare @years as int
, @months as int
, @days as int

select @years, @months, @days```
That does not work for e.g. @adate = '2010-12-01'

5. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369
Originally Posted by xnanx
should be
9 YIL , 1 Ay, 28 Gün
No, it should be
9 YIL , 1 Ay, 26 Gün

6. Registered User
Join Date
Nov 2004
Posts
1,428
This should work:
Code:
```CREATE FUNCTION dbo.YrMonthDayDiff (
@StartDate	DATE,
@EndDate	DATE,
@YrStr		NVARCHAR(10),
@MnthStr	NVARCHAR(10),
@DayStr		NVARCHAR(10)
)
RETURNS NVARCHAR(50)
AS
BEGIN
if @StartDate IS NULL OR @EndDate IS NULL
RETURN(NULL)

DECLARE @Years	int
DECLARE @Months	int
DECLARE @Days int

SET @Days = DATEDIFF(DAY, @StartDate , @EndDate)
SET @Years = @Days / 365
SET @Days = @Days % 365
SET @Months = @Days / 30
SET @Days = @Days % 30

RETURN(	RTRIM(CAST(@Years AS VARCHAR(5))) + @YrStr +
RTRIM(CAST(@Months AS VARCHAR(5))) + @MnthStr +
RTRIM(CAST(@Days AS VARCHAR(5))) + @DayStr);
END;
GO

SELECT Indate, dbo.YrMonthDayDiff(Indate, GetDate(), ' Yil ', ' Ay ', ' Gün ')
FROM #DaTable```

7. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369
Code:
```select from_date, today ,duration=
case when datepart(dd,today)<datepart(dd,from_date) then
convert(varchar(4),(datediff(mm,from_date,today)-1)/12)+' YIL '
+convert(varchar(2),(datediff(mm,from_date,today)-1)%12)+' Ay '
else
convert(varchar(4),datediff(mm,from_date,today)/12) +' YIL '
+convert(varchar(2),datediff(mm,from_date,today)%12) +' Ay '
end
from -- test_data
(
from   master..spt_values a
cross  join master..spt_values b
where  a.type='P'
and  a.number<70
and  b.type='P'
and  b.number<70
)  test_data```

8. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369
Wim, not all days has 30 days and not all years has 365 days

9. Registered User
Join Date
Nov 2004
Posts
1,428
Originally Posted by pdreyer
Wim, not all days has 30 days and not all years has 365 days
I know.

Your solution is better than mine. Nice!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•