Search Postgresql Archives

Re: #PERSONAL# Reg: date going as 01/01/0001

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

Yes true. I agree with you.

I am porting my application from oracle to postgresql. in oracle it enters as NULL and hence this problem was not faced.

Now my problem is that my bind variables are of string type and when the get a NULL value is converts it into an empty string in C++ programming.

Example:
insert into check_date values (to_date(:h1,'yyyymmddhh24miss'),1,to_date(:h2,'yyyymmddhh24miss'));

now h1 and h2 are of string datatype in my c++ program.

If no value is there, it is passed on as a empty string into my query.

How can I handle this to enter NULL values?



Thanks & Regards
Medhavi Mahansaria
Tata Consultancy Services Limited
Unit-VI, No.78, 79& 83,
L-Centre, EPIP Industrial Estate,
Whitefield
Bangalore - 560066,Karnataka
India
Ph:- +91 80 67253769
Cell:- +91 9620053040
Mailto: medhavi.mahansaria@xxxxxxx
Website:
http://www.tcs.com
____________________________________________
Experience certainty.        IT Services
                       Business Solutions
                       Consulting
____________________________________________




From:        Bill Moran <wmoran@xxxxxxxxxxxxxxxxx>
To:        Medhavi Mahansaria <medhavi.mahansaria@xxxxxxx>
Cc:        pgsql-general@xxxxxxxxxxxxxx
Date:        03/06/2015 03:36 PM
Subject:        Re: #PERSONAL# Reg: date going as 01/01/0001




On Fri, 6 Mar 2015 15:24:28 +0530
Medhavi Mahansaria <medhavi.mahansaria@xxxxxxx> wrote:

> Hi Bill,
>
> Here are the details of the table and the query i want to insert.
>
>
> aml_db=> \d+ check_date
>                                  Table "public.check_date"
>   Column   |            Type             | Modifiers | Storage | Stats
> target | Description
> -----------+-----------------------------+-----------+---------+--------------+-------------
>  some_date | date                        |           | plain   |    |
>  sno       | integer                     |           | plain   |    |
>  new_date  | timestamp without time zone |           | plain   |    |
> Has OIDs: no
>
>
> aml_db=> select * from check_date;
>  some_date | sno | new_date
> -----------+-----+----------
> (0 rows)
>
>
> aml_db=> insert into check_date values
> (to_date('','yyyymmddhh24miss'),1,to_date('','yyyymmddhh24miss'));
> INSERT 0 1
> aml_db=> select * from check_date;
>    some_date   | sno |        new_date
> ---------------+-----+------------------------
>  01/01/0001 BC |   1 | 01/01/0001 00:00:00 BC
> (1 row)
>
>
> I need to enter NULL as my date. but since I am getting these variables
> into the bind variables as empty string that is this issue is happening.

You're not inserting null, so it's hard to expect null to be the
result. Try:

INSERT INTO check_date VALUES (null, 1, null);

or even:

INSERT INTO check_date
VALUES (to_date(null, 'yyyymmddhh24miss'), 1, to_date(null, 'yyyymmddhh24miss'));

both of which result in what you desire.

null and the empty string are not the same thing.

--
Bill Moran

=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux