pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDate time value error in Ms Access using pass through queries
6+ messages / 2 participants
[nested] [flat]
* Date time value error in Ms Access using pass through queries
@ 2011-09-21 10:08 c k <shreeseva.learning@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: c k @ 2011-09-21 10:08 UTC (permalink / raw)
To: pgsql-admin
Hello,
I got a error yesterday while I have updated my development database to 9.1
from 9.0.4. I backed up my database from 9.0 and restored in 9.1. It well
successfully. When running a query which calls a function, having date value
as IN parameter, it gives me the error as date/time value out of range. But
when same query is executed using a database from 9.0.4 it runs successfully
without any error. Both databases have a variable 'DateStyle=DMY' set. All
connection parameters are same for both. What is the problem? Does some
thing is changed in 9.1 or I have to wait till new ODBC driver is released?
I am using four digit year format and regional settings are set to use
dd/mm/yyyy style on windows and MS access uses them. Please give your reply.
Thanks and regards,
C P Kulkarni
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [GENERAL] Date time value error in Ms Access using pass through queries
@ 2011-09-21 13:35 Adrian Klaver <adrian.klaver@gmail.com>
parent: c k <shreeseva.learning@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Adrian Klaver @ 2011-09-21 13:35 UTC (permalink / raw)
To: pgsql-general@postgresql.org; +Cc: c k <shreeseva.learning@gmail.com>; pgsql-admin
On Wednesday, September 21, 2011 3:08:11 am c k wrote:
> Hello,
>
> I got a error yesterday while I have updated my development database to 9.1
> from 9.0.4. I backed up my database from 9.0 and restored in 9.1. It well
> successfully. When running a query which calls a function, having date
> value as IN parameter, it gives me the error as date/time value out of
> range. But when same query is executed using a database from 9.0.4 it runs
> successfully without any error. Both databases have a variable
> 'DateStyle=DMY' set. All connection parameters are same for both. What is
> the problem? Does some thing is changed in 9.1 or I have to wait till new
> ODBC driver is released? I am using four digit year format and regional
> settings are set to use dd/mm/yyyy style on windows and MS access uses
> them. Please give your reply.
What is the actual error message?
How did you install the 9.1 versus 9.0 databases?
Related to the above question do the 9.0 and 9.1 have the same storage type
(float or integer) for datetime values?
>
> Thanks and regards,
>
> C P Kulkarni
--
Adrian Klaver
adrian.klaver@gmail.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [GENERAL] Date time value error in Ms Access using pass through queries
@ 2011-09-22 09:25 c k <shreeseva.learning@gmail.com>
parent: Adrian Klaver <adrian.klaver@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: c k @ 2011-09-22 09:25 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@gmail.com>; +Cc: pgsql-admin
It shows error as 'Date/time value out of range' and gives the actual part
of the sql statement which contains date as the details.
I have installed both databases at different locations on same drive on
Fedora 15 and accessing it from Windows Xp virtual machine.
And both databases from 9.0. and 9.1 have same structure and and it is found
that this problem comes for all date columns for 9.1. There is no such
problem for 9.0 at all.
When same query is executed from PgAdmin it executed successfully for 9.1.
So the error may be in ODBC driver and some things related to date data type
may be changed in 9.1.
C P Kulkarni
On Wed, Sep 21, 2011 at 7:05 PM, Adrian Klaver <adrian.klaver@gmail.com>wrote:
> On Wednesday, September 21, 2011 3:08:11 am c k wrote:
> > Hello,
> >
> > I got a error yesterday while I have updated my development database to
> 9.1
> > from 9.0.4. I backed up my database from 9.0 and restored in 9.1. It well
> > successfully. When running a query which calls a function, having date
> > value as IN parameter, it gives me the error as date/time value out of
> > range. But when same query is executed using a database from 9.0.4 it
> runs
> > successfully without any error. Both databases have a variable
> > 'DateStyle=DMY' set. All connection parameters are same for both. What is
> > the problem? Does some thing is changed in 9.1 or I have to wait till new
> > ODBC driver is released? I am using four digit year format and regional
> > settings are set to use dd/mm/yyyy style on windows and MS access uses
> > them. Please give your reply.
>
>
> What is the actual error message?
>
> How did you install the 9.1 versus 9.0 databases?
>
> Related to the above question do the 9.0 and 9.1 have the same storage type
> (float or integer) for datetime values?
>
>
> >
> > Thanks and regards,
> >
> > C P Kulkarni
>
> --
> Adrian Klaver
> adrian.klaver@gmail.com
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [GENERAL] Date time value error in Ms Access using pass through queries
@ 2011-09-22 14:12 Adrian Klaver <adrian.klaver@gmail.com>
parent: c k <shreeseva.learning@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Adrian Klaver @ 2011-09-22 14:12 UTC (permalink / raw)
To: c k <shreeseva.learning@gmail.com>; +Cc: pgsql-admin
On Thursday, September 22, 2011 2:25:40 am c k wrote:
> It shows error as 'Date/time value out of range' and gives the actual part
> of the sql statement which contains date as the details.
What program threw the error, Access,ODBC or Postgres?
>
> I have installed both databases at different locations on same drive on
> Fedora 15 and accessing it from Windows Xp virtual machine.
> And both databases from 9.0. and 9.1 have same structure and and it is
> found that this problem comes for all date columns for 9.1. There is no
> such problem for 9.0 at all.
Define install, from source or package?
>
> When same query is executed from PgAdmin it executed successfully for 9.1.
> So the error may be in ODBC driver and some things related to date data
> type may be changed in 9.1.
What version of the ODBC driver are using?
FYI, there is a Postgres ODBC list:
http://archives.postgresql.org/pgsql-odbc/
>
> C P Kulkarni
>
--
Adrian Klaver
adrian.klaver@gmail.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [GENERAL] Date time value error in Ms Access using pass through queries
@ 2011-09-23 10:13 c k <shreeseva.learning@gmail.com>
parent: Adrian Klaver <adrian.klaver@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: c k @ 2011-09-23 10:13 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@gmail.com>; +Cc: pgsql-admin
As I have connected to postgres from MS Access, it thrown the error.
Now I have updated ODBC driver, still same problems comes for 9.1 and not
for 9.0. There should be some thing that has changed in 9.1 release which
prevents from auto-conversion of format of date from client to server.
From package I have installed postgres 9.1 using EnterpriseDB one click
installer.
ODBC driver has version 9.0.3.10
Should I file a bug for this?
Regards,
C P Kulkarni
On Thu, Sep 22, 2011 at 7:42 PM, Adrian Klaver <adrian.klaver@gmail.com>wrote:
> On Thursday, September 22, 2011 2:25:40 am c k wrote:
> > It shows error as 'Date/time value out of range' and gives the actual
> part
> > of the sql statement which contains date as the details.
>
> What program threw the error, Access,ODBC or Postgres?
>
> >
> > I have installed both databases at different locations on same drive on
> > Fedora 15 and accessing it from Windows Xp virtual machine.
> > And both databases from 9.0. and 9.1 have same structure and and it is
> > found that this problem comes for all date columns for 9.1. There is no
> > such problem for 9.0 at all.
>
> Define install, from source or package?
>
> >
> > When same query is executed from PgAdmin it executed successfully for
> 9.1.
> > So the error may be in ODBC driver and some things related to date data
> > type may be changed in 9.1.
>
> What version of the ODBC driver are using?
>
> FYI, there is a Postgres ODBC list:
> http://archives.postgresql.org/pgsql-odbc/
>
> >
> > C P Kulkarni
> >
>
>
> --
> Adrian Klaver
> adrian.klaver@gmail.com
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [GENERAL] Date time value error in Ms Access using pass through queries
@ 2011-09-23 11:42 c k <shreeseva.learning@gmail.com>
parent: c k <shreeseva.learning@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: c k @ 2011-09-23 11:42 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@gmail.com>; +Cc: pgsql-admin
I found the solution. Earlier for 9.0 database I have changed a line as
Datestyle= ' ISO, mdy' to DateStyle= "ISO, dmy'. So it was working fine. For
9.1 I forgot to make this change in postgresql.conf. After making the change
everything is working fine.
Regards,
C P Kulkarni
On Fri, Sep 23, 2011 at 3:43 PM, c k <shreeseva.learning@gmail.com> wrote:
> As I have connected to postgres from MS Access, it thrown the error.
> Now I have updated ODBC driver, still same problems comes for 9.1 and not
> for 9.0. There should be some thing that has changed in 9.1 release which
> prevents from auto-conversion of format of date from client to server.
> From package I have installed postgres 9.1 using EnterpriseDB one click
> installer.
> ODBC driver has version 9.0.3.10
> Should I file a bug for this?
>
> Regards,
> C P Kulkarni
>
>
> On Thu, Sep 22, 2011 at 7:42 PM, Adrian Klaver <adrian.klaver@gmail.com>wrote:
>
>> On Thursday, September 22, 2011 2:25:40 am c k wrote:
>> > It shows error as 'Date/time value out of range' and gives the actual
>> part
>> > of the sql statement which contains date as the details.
>>
>> What program threw the error, Access,ODBC or Postgres?
>>
>> >
>> > I have installed both databases at different locations on same drive on
>> > Fedora 15 and accessing it from Windows Xp virtual machine.
>> > And both databases from 9.0. and 9.1 have same structure and and it is
>> > found that this problem comes for all date columns for 9.1. There is no
>> > such problem for 9.0 at all.
>>
>> Define install, from source or package?
>>
>> >
>> > When same query is executed from PgAdmin it executed successfully for
>> 9.1.
>> > So the error may be in ODBC driver and some things related to date data
>> > type may be changed in 9.1.
>>
>> What version of the ODBC driver are using?
>>
>> FYI, there is a Postgres ODBC list:
>> http://archives.postgresql.org/pgsql-odbc/
>>
>> >
>> > C P Kulkarni
>> >
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@gmail.com
>>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-09-23 11:42 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-21 10:08 Date time value error in Ms Access using pass through queries c k <shreeseva.learning@gmail.com>
2011-09-21 13:35 ` Adrian Klaver <adrian.klaver@gmail.com>
2011-09-22 09:25 ` c k <shreeseva.learning@gmail.com>
2011-09-22 14:12 ` Adrian Klaver <adrian.klaver@gmail.com>
2011-09-23 10:13 ` c k <shreeseva.learning@gmail.com>
2011-09-23 11:42 ` c k <shreeseva.learning@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox