SQL Anywhere database

Author

date diff

marley

2005-05-23, 8:23 pm

What is the best way to get a difference between 2 dates?
The following [ datediff(year, '1979-09-23', '2005-05-23')
] gives me 26. Shouldn't be 25?

I'm confused...

Glenn Paulley

2005-05-23, 8:23 pm

marley wrote in news:429230b4.3251.1681692777@sybase.com:

> What is the best way to get a difference between 2 dates?
> The following [ datediff(year, '1979-09-23', '2005-05-23')
>] gives me 26. Shouldn't be 25?
>
>
> I'm confused...
>

What version and build of ASA are you using?

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

marley

2005-05-23, 8:23 pm

I'm using ASA 9.0.0 build 1108.

> marley wrote in news:429230b4.3251.1681692777@sybase.com:
>
> '2005-05-23') >] gives me 26. Shouldn't be 25?
>
> What version and build of ASA are you using?
>
> --
> Glenn Paulley
> Research and Development Manager, Query Processing
> iAnywhere Solutions Engineering
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports:
> http://casexpress.sybase.com/cx/cx.stm
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288

Stephen Rice

2005-05-23, 8:23 pm

Nope that is the way it is supposed to work (the docs don't explicitly say
years work this way but every other unit does)

I find it simplest to think of Datediff() as a shortcut for:
select datepart (year, '2005-05-23') - datepart(year, '1979-09-23')

/steve
--
Stephen Rice
Technical Services Manager
iAnywhere Solutions
------------ Please Post -------------
<marley> wrote in message news:429230b4.3251.1681692777@sybase.com...
> What is the best way to get a difference between 2 dates?
> The following [ datediff(year, '1979-09-23', '2005-05-23')
> ] gives me 26. Shouldn't be 25?
>
>
> I'm confused...

marley

2005-05-24, 9:23 am

What's the best approach for this? Use datediff(month,
start_date, end_date) / 12 perhaps?

> Nope that is the way it is supposed to work (the docs
> don't explicitly say years work this way but every other
> unit does)
>
> I find it simplest to think of Datediff() as a shortcut
> for: select datepart (year, '2005-05-23') - datepart(year
> , '1979-09-23')
>
> /steve
> --
> Stephen Rice
> Technical Services Manager
> iAnywhere Solutions
> ------------ Please Post -------------
> <marley> wrote in message
> gives me 26. Shouldn't be 25? >
>
>

Bruce Hay

2005-05-24, 1:23 pm

See the AgeInYears function in CodeXchange (extracted below).

Notes:
------
ASA does not have a built-in function which calculates
ages. The datediff(year,...) function counts the
number of "year boundaries" that are between two
dates, which is not the same. Below is a user function
which can be used to calculate ages. It takes two
parameters, a starting and ending date. If the second
parameter is omitted, the current date is used for the
calculation.
-------------------
--SQL Script Starts Here

call dbo. sa_make_object('func
tion','AgeInYears','
dbo')
go

alter function dbo.AgeInYears( beg_date date, end_date
date default current date )
returns int
begin
return datediff(year,beg_da
te,end_date) -
if(dateformat(beg_da
te,'mmdd') >
dateformat(end_date,
'mmdd') ) then 1 else 0 endif
end
go
grant execute on dbo.AgeInYears to PUBLIC
go

// Example:
select 'I was born on 1980/06/25, so today I am ' ||
AgeInYears('1980/06/25') || ' years old'

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer
<marley> wrote in message news:429337ed.3a77.1681692777@sybase.com...[color=darkred]
> What's the best approach for this? Use datediff(month,
> start_date, end_date) / 12 perhaps?
>
>

Sponsored Links