TIMESTAMP/DATE Format & INSERT

[Andreas Goldstein]

I am porting a database from Access to MaxDB. From the Access-DB I have made
a SQL-Dump that contains about 800 dates with a date in it. The date-format
in the dump-fil is "DD.MM.YYYY HH:MM:SS".

I am now trying to insert these lines into the MaxDB-DB by using the
SQL-Studio.

For example the following codes demonstrates what I am trying to do:
CREATE TABLE aaa (
id FIXED(10) DEFAULT SERIAL,
sent TIMESTAMP,
PRIMARY KEY (id))
//
INSERT INTO aaa (sent) VALUES ('31.03.2005 13:35:21')
...


Unfourtunately this does not work. I think that one point is a wrong
date-format that should YYYY-MM-DD because of the ISO-mode. From the manual
I do not understand, where I can change the ISO-mode to an other that meets
my requirements.


I would be happy if anyone can help me - thanks!

[Kevin Wilson]

You are better off just changing your format to meet their's. I have
struggled long and hard with their dates functionality (or lack thereof) and
it isn't worth your time.

-----Original Message-----
From: Andreas Goldstein [mailto:***@gmx.li]
Sent: Thursday, March 31, 2005 4:45 PM
To: ***@lists.mysql.com
Subject: TIMESTAMP/DATE Format & INSERT


I am porting a database from Access to MaxDB. From the Access-DB I have made
a SQL-Dump that contains about 800 dates with a date in it. The date-format
in the dump-fil is "DD.MM.YYYY HH:MM:SS".

I am now trying to insert these lines into the MaxDB-DB by using the
SQL-Studio.

For example the following codes demonstrates what I am trying to do:
CREATE TABLE aaa (
id FIXED(10) DEFAULT SERIAL,
sent TIMESTAMP,
PRIMARY KEY (id))
//
INSERT INTO aaa (sent) VALUES ('31.03.2005 13:35:21')
...


Unfourtunately this does not work. I think that one point is a wrong
date-format that should YYYY-MM-DD because of the ISO-mode. From the manual
I do not understand, where I can change the ISO-mode to an other that meets
my requirements.


I would be happy if anyone can help me - thanks!
--
Sparen beginnt mit GMX DSL: http://www.gmx.net/de/go/dsl
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:
http://lists.mysql.com/maxdb?unsub=***@comtrol.com

[Kevin Wilson]

The biggest problem I have is with the jdbc driver and how it (doesn't)
handle implied date format conversions. For instance, if you use the Now()
function in VB it spits out a timestamp value in the format of "MM/DD/YYYY
HH:MM AM/PM" and when assigned to a timestamp field the ODBC driver handles
the interpretation/conversion easily but using that same format with the
jdbc driver causes an error. I am not sure if it the MSADO library or the
MaxDB-ODBC driver that is reworking the datetime to fit the target format
but it sure would be nice if the jdbc driver could do the same.

-----Original Message-----
From: Zabach, Elke [mailto:***@sap.com]
Sent: Friday, April 01, 2005 12:51 AM
To: Andreas Goldstein; ***@lists.mysql.com
Subject: AW: TIMESTAMP/DATE Format & INSERT

Post by Andreas Goldstein
I am porting a database from Access to MaxDB. From the Access-DB I

have

Post by Andreas Goldstein
made
a SQL-Dump that contains about 800 dates with a date in it. The date-
format
in the dump-fil is "DD.MM.YYYY HH:MM:SS".
I am now trying to insert these lines into the MaxDB-DB by using the
SQL-Studio.
CREATE TABLE aaa (
id FIXED(10) DEFAULT SERIAL,
sent TIMESTAMP,
PRIMARY KEY (id))
//
INSERT INTO aaa (sent) VALUES ('31.03.2005 13:35:21')
...
Unfourtunately this does not work. I think that one point is a wrong
date-format that should YYYY-MM-DD because of the ISO-mode. From the
manual
I do not understand, where I can change the ISO-mode to an other that
meets
my requirements.
I would be happy if anyone can help me - thanks!

the kernel is able to handle different date-time-formats. But
unfortunately ODBC (and SQLStudio uses it) defines its own
date-time-format looking like ISO.
Therefore the kernel has to use ISO if ODBC/SQLStudio is talking to him
--> causing the mentioned trouble for users wanting to use a different
format.

Elke
SAP Labs Berlin

Post by Andreas Goldstein
--
Sparen beginnt mit GMX DSL: http://www.gmx.net/de/go/dsl
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb

http://lists.mysql.com/maxdb?unsub=***@sap.com
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:
http://lists.mysql.com/maxdb?unsub=***@comtrol.com

[Schroeder, Alexander]

Hello Kevin,

The ODBC driver (and the JDBC driver too) do not modify any strings
or parameters. IIRC, the result of Now() in VB isn't a string, but
a DateTime value, which is a VB-internal representation of a time
stamp, which is then handled by VB/MSADO so that it works with
the ODBC format. (Take also care that what you see a string represen-
tation depends on your local - I'm quite sure with a German windows
you see DD.MM.YYYY HH:MM (in 24h format).).

For Java I can only suggest using a java.text.SimpleDateFormat instance
that is setup for your format, and create Date object by using the dateformat's
parse method. (and use later 'setDate' method for setting that parameter).

Regards
Alexander Schröder
SAP DB, SAP Labs Berlin

-----Original Message-----
From: Kevin Wilson [mailto:***@comtrol.com]
Sent: Freitag, 1. April 2005 19:18
To: Zabach, Elke; Andreas Goldstein; ***@lists.mysql.com
Subject: RE: TIMESTAMP/DATE Format & INSERT


The biggest problem I have is with the jdbc driver and how it (doesn't)
handle implied date format conversions. For instance, if you use the Now()
function in VB it spits out a timestamp value in the format of "MM/DD/YYYY
HH:MM AM/PM" and when assigned to a timestamp field the ODBC driver handles
the interpretation/conversion easily but using that same format with the
jdbc driver causes an error. I am not sure if it the MSADO library or the
MaxDB-ODBC driver that is reworking the datetime to fit the target format
but it sure would be nice if the jdbc driver could do the same.

-----Original Message-----
From: Zabach, Elke [mailto:***@sap.com]
Sent: Friday, April 01, 2005 12:51 AM
To: Andreas Goldstein; ***@lists.mysql.com
Subject: AW: TIMESTAMP/DATE Format & INSERT

Post by Andreas Goldstein
I am porting a database from Access to MaxDB. From the Access-DB I

have

Post by Andreas Goldstein
made
a SQL-Dump that contains about 800 dates with a date in it. The date-
format
in the dump-fil is "DD.MM.YYYY HH:MM:SS".
I am now trying to insert these lines into the MaxDB-DB by using the
SQL-Studio.
CREATE TABLE aaa (
id FIXED(10) DEFAULT SERIAL,
sent TIMESTAMP,
PRIMARY KEY (id))
//
INSERT INTO aaa (sent) VALUES ('31.03.2005 13:35:21')
...
Unfourtunately this does not work. I think that one point is a wrong
date-format that should YYYY-MM-DD because of the ISO-mode. From the
manual
I do not understand, where I can change the ISO-mode to an other that
meets
my requirements.
I would be happy if anyone can help me - thanks!

the kernel is able to handle different date-time-formats. But
unfortunately ODBC (and SQLStudio uses it) defines its own
date-time-format looking like ISO.
Therefore the kernel has to use ISO if ODBC/SQLStudio is talking to him
--> causing the mentioned trouble for users wanting to use a different
format.

Elke
SAP Labs Berlin

Post by Andreas Goldstein
--
Sparen beginnt mit GMX DSL: http://www.gmx.net/de/go/dsl
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb

http://lists.mysql.com/maxdb?unsub=***@sap.com
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:
http://lists.mysql.com/maxdb?unsub=***@comtrol.com
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/maxdb?unsub=***@sap.com