Re: [SQL] Date

Thank you for all your helps.

The 'datetime' is indeed a better choice.
The 'now' is the answer for my question.

Now, I have one question, that I think it is wierd.

create table blah
(
custnum int,
startdate datetime default datetime(now()),
enddate datetime
);

FIRST:
insert into blah
(custnum, enddate)
values
(1,'01-JAN-00');

SECOND:
insert into blah
(custnum, enddate)
values
(1,'01-01-00');

THIRD:
insert into blah
(custnum, enddate)
values
(1,'01-01-2000');

FOUR:
insert into blah
(custnum, enddate)
values
(custnum, '01012000');

The first, second and third does the right thing, which is tyring to insert
01 JANUARY 2000, the result in postgres is :
Sat Jan 01 00:00:00 2000

However, the fourth is wrong, the result in postgres is :
Wed Aug 02 00:00:00 0102

Could someone tell me why?

Regards
Chai

Aleksey Dashevsky wrote:

> On Wed, 8 Jul 1998, Chairudin Sentosa Harjo wrote:
>
> > Hello,
> >
> > I need help with date.
> >
> > create table blah
> > (
> > custnum int,
> > startdate date,
> > enddate date
> > );
> >
> > How do I get the "real date" to insert to startdate when
> > the data is entered?
> >
> > insert into blah
> > (custnum, startdate, enddate)
> > values
> > (001,???,'NULL');
> >
> > I need a way to fill in the '???' field.
> > In oracle I could use
> > select sysdate from dual;
>
> There are some different ways:
> 1. You can try to use current_date and current_time system varibales.
> 2. You can use constant 'now' casted to appropriate data type, e.g. :
> template1=> select 'now'::date;
> ?column?
> ----------
> 08-07-1998
> (1 row)
>
> template1=> select 'now'::datetime;
> ?column?
> ----------------------------
> Wed 08 Jul 12:39:22 1998 IDT
> (1 row)
>
> template1=> select 'now'::time;
> ?column?
> --------
> 12:39:25
> (1 row)
>
> 3. There is also now() function:
>
> template1=> select now();
> now
> ----------------------
> 1998-07-08 12:39:47+03
> (1 row)
>
> It is almost the same as constant 'now', but if you want to create table
> which will have auto-timestamp-field, you need following syntax:
>
> proba=> create table proba (i int, tt datetime default datetime(now()));
> CREATE
>
> proba=> insert into proba values (1);
> INSERT 2170370 1
> proba=> insert into proba values (2);
> INSERT 2170371 1
> proba=> insert into proba values (7);
> INSERT 2170372 1
> proba=> insert into proba values (989);
> INSERT 2170373 1
> proba=> select * from proba;
> i|tt
> ---+----------------------------
> 1|Wed 08 Jul 12:54:53 1998 IDT
> 2|Wed 08 Jul 12:54:56 1998 IDT
> 7|Wed 08 Jul 12:54:58 1998 IDT
> 989|Wed 08 Jul 12:55:02 1998 IDT
> (4 rows)