Results 1 to 9 of 9
  1. #1
    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. #2
    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. #3
    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
    set @adate = '2010-01-01'
    
    select getdate(), @adate
    
    declare @years as int
    , @months as int
    , @days as int
    
    select @years=datediff(year, @adate, getdate())
    select @adate=dateadd(year, @years, @adate)
    select @months=datediff(month, @adate, getdate())
    select @adate=dateadd(month, @months, @adate)
    select @days=datediff(day, @adate, getdate())
    select @adate=dateadd(day, @days, @adate)
    select @years, @months, @days

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by Kaiowas View Post
    If yes, I guess I would create a function that would do something like this:

    Code:
    declare @adate as datetime
    set @adate = '2010-01-01'
    
    select getdate(), @adate
    
    declare @years as int
    , @months as int
    , @days as int
    
    select @years=datediff(year, @adate, getdate())
    select @adate=dateadd(year, @years, @adate)
    select @months=datediff(month, @adate, getdate())
    select @adate=dateadd(month, @months, @adate)
    select @days=datediff(day, @adate, getdate())
    select @adate=dateadd(day, @days, @adate)
    select @years, @months, @days
    That does not work for e.g. @adate = '2010-12-01'

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by xnanx View Post
    should be
    9 YIL , 1 Ay, 28 Gün
    No, it should be
    9 YIL , 1 Ay, 26 Gün

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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 '
      +convert(varchar(2),datediff(dd,dateadd(mm,(datediff(mm,from_date,today)-1),from_date),today))+' Gün'
    else 
       convert(varchar(4),datediff(mm,from_date,today)/12) +' YIL '
      +convert(varchar(2),datediff(mm,from_date,today)%12) +' Ay '
      +convert(varchar(2),datediff(dd,dateadd(mm,(datediff(mm,from_date,today)),from_date),today)) +' Gün'
    end
    from -- test_data
      (
       select from_date=dateadd(dd,-a.number*70-b.number,getdate()), today=getdate()
       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. #8
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Wim, not all days has 30 days and not all years has 365 days

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by pdreyer View Post
    Wim, not all days has 30 days and not all years has 365 days
    I know.

    Your solution is better than mine. Nice!
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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