Re: Dateadd

I need to know if there is a sql function implemented in postgres that gives
me a date plus any number of days, months or years (the traditional dateadd
function) or how to do it in sql?

Thanks in advance for any help

Ligia

>>>>> "LP" == Ligia Pimentel <lmpimentel(at)yahoo(dot)com> writes:

LP> I need to know if there is a sql function implemented in postgres
LP> that gives me a date plus any number of days, months or years (the
LP> traditional dateadd function) or how to do it in sql?

Here's what I use:

select CURRENT_DATE + '4 DAYS'::interval;

Just cast your "interval" from a string to an interval type, then add
it to your date value.

I doubt this is portable. What "tradition" provides for a dateadd()
function?

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera(at)kciLink(dot)com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

On Wed, 2 May 2001, Ligia Pimentel wrote:

> I need to know if there is a sql function implemented in postgres that gives
> me a date plus any number of days, months or years (the traditional dateadd
> function) or how to do it in sql?

Just add a date and an interval. Something like:
date + '1 day'::interval

* Ligia Pimentel <lmpimentel(at)yahoo(dot)com> menulis:
> I need to know if there is a sql function implemented in postgres that gives
> me a date plus any number of days, months or years (the traditional dateadd
> function) or how to do it in sql?

cepat=# select '01-01-00'::date + '2 months 10 days'::interval;
?column?
------------------------
2000-03-11 00:00:00-05
(1 row)

--
Cliff Crawford http://www.sowrong.org/
birthday party cheesecake jellybean BOOM

Folks,

FYI - this difference in syntax is because PostgreSQL implements real
SQL operators (i.e. what the operator does and what arguments it takes
depend on the data types involved) and Microsoft does not (in MS SQL,
operators mean the same thing for almost all data types).

Speaking of which, why doesn't PostgreSQL allow the use of "+" between
two VARCHARs to mean the same as "||" ? Is there an addition operation
for PgSQL for VARCHAR other than concatination?

-Josh

> > I need to know if there is a sql function implemented in postgres
> that gives
> > me a date plus any number of days, months or years (the traditional
> dateadd
> > function) or how to do it in sql?

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco

"Josh Berkus" <josh(at)agliodbs(dot)com> writes:
> Speaking of which, why doesn't PostgreSQL allow the use of "+" between
> two VARCHARs to mean the same as "||" ?

You mean, other than the fact that "||" is the SQL-standard string
concatenation operator and "+" isn't?

I'm not eager to follow the pied pipers from Redmond down the garden
path to completely standards-ignorant products. *Someone's* got to
stick up for standards compliance. Microsoft has a vested interest in
locking people into their code via gratuitously nonstandard notations
... but let's not be like them.

There was some talk recently of putting together packages of function
and operator definitions to ease portability from other DBMSes to PG.
I'd have no objection to including "+ as string concatenation" in a
set of operators that're clearly marked as MS-SQL-isms. But I don't
think we'd do the world any favor by adopting a clearly nonstandard
notation as part of our standard operator set.

regards, tom lane

> There was some talk recently of putting together packages of function
> and operator definitions to ease portability from other DBMSes to PG.
> I'd have no objection to including "+ as string concatenation" in a
> set of operators that're clearly marked as MS-SQL-isms. But I don't
> think we'd do the world any favor by adopting a clearly nonstandard
> notation as part of our standard operator set.

Now that someone else thinks this is a good idea, I have added to the
TODO list:

* Allow plug-in modules to emulate features from other databases

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026