RE: Urgent : Problems with JDBC Date formats...
In that case perhaps you could try using the SQL TO_DATE function. I'm
not sure what the proper syntax for this is, but in Oracle you could use
something like:
Select * From table WHERE (dateField = TO_DATE('2001/01/01',
'/mm/dd'))
failing that, see if you can change the default date format in the
database.
hth
dim
On Tue, 23 Oct 2001, Shah, Chintan V (Chintan) wrote:
Thanks for the guidance, but the problem is...my whole application is
ready...i'm not using PreparedStatement anywhere... (to be honest, i've
never used also...). And my SQL Statements are built dynamically in my Java
Beans depending on the input coming from the users. So, I would prefer a
solution, which doesn't force me to make drastic changes in my code...
thanks and regards,
Chintan
-Original Message-
From: Dmitri Colebatch [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 23, 2001 11:11 AM
To: '[EMAIL PROTECTED]'
Subject: Re: Urgent : Problems with JDBC Date formats...
I cant guarentee that this will work with Access and SQL server, but the
proper way to do what you're trying to do is use a PreparedStatement with
setDate(2, new Date(2001, 1, 1)).
hth
dim
On Tue, 23 Oct 2001, Shah, Chintan V (Chintan) wrote:
Hi all,
This is a very off-topic post (as perhaps it has nothing to do with
tomcat),
but I hope people would like to guide me on this..
I'm using Tomcat 3.2, Apache 1.3.19, Jdk 1.3, Jdbc-Odbc Driver on Win NT
4.0. I want my application to work with both the databases..i.e. with
Ms-Access 2000 and SQL Server 7.0.
I completed my whole application with Ms-Access, everything working
fine..then I tried with SQL Server 7.0...by exporting databases from
Ms-Access to SQL Server. For most of the parts of my code...which is
interacting with DBs thru JDBC worked fine...except for some syntactical
changes... but the major problem is with field types of *DATE*.
In Ms-Access, the field type is Date/Time - short date and in SQL server
it is smalldatetime.
E.g. SQL Stmt : Select * From table WHERE (dateField = #2001/01/01#)
gives desired results in MS-Access, but when the DB is SQL Server, it
gives
error.
And the same way, SQL Stmt : Select * From table WHERE (dateField =
'2001/01/01') gives desired results in SQL Server, but when the DB is
Ms-Access, it gives syntax error, saying that data types mismatch in
criteria expression.
So, how should I deal with these types of things in my code, to make it
compatible with Ms-Access and SQL Server as well.
Your guidance will be a great help for me.
thanks and regards,
Chintan.
AW: Urgent : Problems with JDBC Date formats...
The proper jdbc way to do that, is to use the escape syntax:
for dates:
where datefield = {d '-mm-dd'}
for timestamps:
where datefield = {ts '-mm-dd hh:mm:ss[.f]'}
-Ursprüngliche Nachricht-
Von: Dmitri Colebatch [mailto:[EMAIL PROTECTED]]
Gesendet: Dienstag, 23. Oktober 2001 08:44
An: Shah, Chintan V (Chintan)
Cc: [EMAIL PROTECTED]
Betreff: RE: Urgent : Problems with JDBC Date formats...
snip/
Select * From table WHERE (dateField = TO_DATE('2001/01/01',
'/mm/dd'))
snip/
RE: Urgent : Problems with JDBC Date formats...
ya, I know the Escape sequence...but it doesn't work in case of
Ms-Accessgives error..
Malformed GUID. in query expression '(datefield = {d '-mm-dd'})'. Works
with SQL Server.
So, what to do in that case ??
Moreover, as Dmitri suggested...using TO_DATE function...which doesn't
work in Ms-Access/SQL Server Query... is it a function of Java ?? If, then
of which class ?
thanks and regards,
Chintan
-Original Message-
From: Ralph Einfeldt [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 23, 2001 12:26 PM
To: [EMAIL PROTECTED]
Subject: AW: Urgent : Problems with JDBC Date formats...
The proper jdbc way to do that, is to use the escape syntax:
for dates:
where datefield = {d '-mm-dd'}
for timestamps:
where datefield = {ts '-mm-dd hh:mm:ss[.f]'}
-Ursprüngliche Nachricht-
Von: Dmitri Colebatch [mailto:[EMAIL PROTECTED]]
Gesendet: Dienstag, 23. Oktober 2001 08:44
An: Shah, Chintan V (Chintan)
Cc: [EMAIL PROTECTED]
Betreff: RE: Urgent : Problems with JDBC Date formats...
snip/
Select * From table WHERE (dateField = TO_DATE('2001/01/01',
'/mm/dd'))
snip/
RE: Urgent : Problems with JDBC Date formats...
On Tue, 23 Oct 2001, Shah, Chintan V (Chintan) wrote:
ya, I know the Escape sequence...but it doesn't work in case of
Ms-Accessgives error..
Malformed GUID. in query expression '(datefield = {d '-mm-dd'})'. Works
with SQL Server.
I wasn't aware of this, but looks to me to be exactly what you want... I
think the problem you have is the fact that you're trying to use access as
a database...
So, what to do in that case ??
Moreover, as Dmitri suggested...using TO_DATE function...which doesn't
work in Ms-Access/SQL Server Query... is it a function of Java ?? If, then
of which class ?
TO_DATE is an oracle thing I think... but faik it might be part of SQL
cheers
dim
thanks and regards,
Chintan
-Original Message-
From: Ralph Einfeldt [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 23, 2001 12:26 PM
To: [EMAIL PROTECTED]
Subject: AW: Urgent : Problems with JDBC Date formats...
The proper jdbc way to do that, is to use the escape syntax:
for dates:
where datefield = {d '-mm-dd'}
for timestamps:
where datefield = {ts '-mm-dd hh:mm:ss[.f]'}
-Ursprüngliche Nachricht-
Von: Dmitri Colebatch [mailto:[EMAIL PROTECTED]]
Gesendet: Dienstag, 23. Oktober 2001 08:44
An: Shah, Chintan V (Chintan)
Cc: [EMAIL PROTECTED]
Betreff: RE: Urgent : Problems with JDBC Date formats...
snip/
Select * From table WHERE (dateField = TO_DATE('2001/01/01',
'/mm/dd'))
snip/
RE: Urgent : Problems with JDBC Date formats...
At 09:11 AM 23/10/01, you wrote:
ya, I know the Escape sequence...but it doesn't work in case of
Ms-Accessgives error..
Malformed GUID. in query expression '(datefield = {d '-mm-dd'})'. Works
with SQL Server.
So, what to do in that case ??
Moreover, as Dmitri suggested...using TO_DATE function...which doesn't
work in Ms-Access/SQL Server Query... is it a function of Java ?? If, then
of which class ?
We're using PreparedStatementS, coupled with setDate() to set the date. The
database driver takes care of the casting/processing automagically. (Tested
with SQLServer, not access.)
Jim
--
* Jim Cheesman *
Trabajo:
[EMAIL PROTECTED] - (34)(91) 724 9200 x 2360
I disagree
with unanimity.
Re: Urgent : Problems with JDBC Date formats...
Hi !
Standart JDBC Date (java.sql.Date) format : yyy-mm-dd.
- Original Message -
From: Dmitri Colebatch [EMAIL PROTECTED]
To: Shah, Chintan V (Chintan) [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday, October 23, 2001 9:43 AM
Subject: RE: Urgent : Problems with JDBC Date formats...
In that case perhaps you could try using the SQL TO_DATE function. I'm
not sure what the proper syntax for this is, but in Oracle you could use
something like:
Select * From table WHERE (dateField = TO_DATE('2001/01/01',
'/mm/dd'))
failing that, see if you can change the default date format in the
database.
hth
dim
On Tue, 23 Oct 2001, Shah, Chintan V (Chintan) wrote:
Thanks for the guidance, but the problem is...my whole application is
ready...i'm not using PreparedStatement anywhere... (to be honest, i've
never used also...). And my SQL Statements are built dynamically in my
Java
Beans depending on the input coming from the users. So, I would prefer a
solution, which doesn't force me to make drastic changes in my code...
thanks and regards,
Chintan
-Original Message-
From: Dmitri Colebatch [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 23, 2001 11:11 AM
To: '[EMAIL PROTECTED]'
Subject: Re: Urgent : Problems with JDBC Date formats...
I cant guarentee that this will work with Access and SQL server, but the
proper way to do what you're trying to do is use a PreparedStatement
with
setDate(2, new Date(2001, 1, 1)).
hth
dim
On Tue, 23 Oct 2001, Shah, Chintan V (Chintan) wrote:
Hi all,
This is a very off-topic post (as perhaps it has nothing to do with
tomcat),
but I hope people would like to guide me on this..
I'm using Tomcat 3.2, Apache 1.3.19, Jdk 1.3, Jdbc-Odbc Driver on Win
NT
4.0. I want my application to work with both the databases..i.e. with
Ms-Access 2000 and SQL Server 7.0.
I completed my whole application with Ms-Access, everything working
fine..then I tried with SQL Server 7.0...by exporting databases from
Ms-Access to SQL Server. For most of the parts of my code...which is
interacting with DBs thru JDBC worked fine...except for some
syntactical
changes... but the major problem is with field types of *DATE*.
In Ms-Access, the field type is Date/Time - short date and in SQL
server
it is smalldatetime.
E.g. SQL Stmt : Select * From table WHERE (dateField =
#2001/01/01#)
gives desired results in MS-Access, but when the DB is SQL Server, it
gives
error.
And the same way, SQL Stmt : Select * From table WHERE (dateField
=
'2001/01/01') gives desired results in SQL Server, but when the DB is
Ms-Access, it gives syntax error, saying that data types mismatch in
criteria expression.
So, how should I deal with these types of things in my code, to make
it
compatible with Ms-Access and SQL Server as well.
Your guidance will be a great help for me.
thanks and regards,
Chintan.
Re: Urgent : Problems with JDBC Date formats...
When did JDBC began to use the 1-digit centry code and
2-digit year code? So it's 001, not 2001 :-)
Pae
Hi !
Standart JDBC Date (java.sql.Date) format : yyy-mm-dd.
- Original Message -
From: Dmitri Colebatch [EMAIL PROTECTED]
To: Shah, Chintan V (Chintan) [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday, October 23, 2001 9:43 AM
Subject: RE: Urgent : Problems with JDBC Date formats...
In that case perhaps you could try using the SQL TO_DATE function. I'm
not sure what the proper syntax for this is, but in Oracle you could use
something like:
Select * From table WHERE (dateField = TO_DATE('2001/01/01',
'/mm/dd'))
failing that, see if you can change the default date format in the
database.
hth
dim
On Tue, 23 Oct 2001, Shah, Chintan V (Chintan) wrote:
Thanks for the guidance, but the problem is...my whole application is
ready...i'm not using PreparedStatement anywhere... (to be honest, i've
never used also...). And my SQL Statements are built dynamically in my
Java
Beans depending on the input coming from the users. So, I would prefer
a
solution, which doesn't force me to make drastic changes in my code...
thanks and regards,
Chintan
-Original Message-
From: Dmitri Colebatch [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 23, 2001 11:11 AM
To: '[EMAIL PROTECTED]'
Subject: Re: Urgent : Problems with JDBC Date formats...
I cant guarentee that this will work with Access and SQL server, but
the
proper way to do what you're trying to do is use a PreparedStatement
with
setDate(2, new Date(2001, 1, 1)).
hth
dim
On Tue, 23 Oct 2001, Shah, Chintan V (Chintan) wrote:
Hi all,
This is a very off-topic post (as perhaps it has nothing to do with
tomcat),
but I hope people would like to guide me on this..
I'm using Tomcat 3.2, Apache 1.3.19, Jdk 1.3, Jdbc-Odbc Driver on Win
NT
4.0. I want my application to work with both the databases..i.e.
with
Ms-Access 2000 and SQL Server 7.0.
I completed my whole application with Ms-Access, everything working
fine..then I tried with SQL Server 7.0...by exporting databases from
Ms-Access to SQL Server. For most of the parts of my code...which is
interacting with DBs thru JDBC worked fine...except for some
syntactical
changes... but the major problem is with field types of *DATE*.
In Ms-Access, the field type is Date/Time - short date and in SQL
server
it is smalldatetime.
E.g. SQL Stmt : Select * From table WHERE (dateField =
#2001/01/01#)
gives desired results in MS-Access, but when the DB is SQL Server, it
gives
error.
And the same way, SQL Stmt : Select * From table WHERE
(dateField
=
'2001/01/01') gives desired results in SQL Server, but when the DB
is
Ms-Access, it gives syntax error, saying that data types mismatch in
criteria expression.
So, how should I deal with these types of things in my code, to make
it
compatible with Ms-Access and SQL Server as well.
Your guidance will be a great help for me.
thanks and regards,
Chintan.
Urgent : Problems with JDBC Date formats...
Hi all, This is a very off-topic post (as perhaps it has nothing to do with tomcat), but I hope people would like to guide me on this.. I'm using Tomcat 3.2, Apache 1.3.19, Jdk 1.3, Jdbc-Odbc Driver on Win NT 4.0. I want my application to work with both the databases..i.e. with Ms-Access 2000 and SQL Server 7.0. I completed my whole application with Ms-Access, everything working fine..then I tried with SQL Server 7.0...by exporting databases from Ms-Access to SQL Server. For most of the parts of my code...which is interacting with DBs thru JDBC worked fine...except for some syntactical changes... but the major problem is with field types of *DATE*. In Ms-Access, the field type is Date/Time - short date and in SQL server it is smalldatetime. E.g. SQL Stmt : Select * From table WHERE (dateField = #2001/01/01#) gives desired results in MS-Access, but when the DB is SQL Server, it gives error. And the same way, SQL Stmt : Select * From table WHERE (dateField = '2001/01/01') gives desired results in SQL Server, but when the DB is Ms-Access, it gives syntax error, saying that data types mismatch in criteria expression. So, how should I deal with these types of things in my code, to make it compatible with Ms-Access and SQL Server as well. Your guidance will be a great help for me. thanks and regards, Chintan.
Re: Urgent : Problems with JDBC Date formats...
I cant guarentee that this will work with Access and SQL server, but the proper way to do what you're trying to do is use a PreparedStatement with setDate(2, new Date(2001, 1, 1)). hth dim On Tue, 23 Oct 2001, Shah, Chintan V (Chintan) wrote: Hi all, This is a very off-topic post (as perhaps it has nothing to do with tomcat), but I hope people would like to guide me on this.. I'm using Tomcat 3.2, Apache 1.3.19, Jdk 1.3, Jdbc-Odbc Driver on Win NT 4.0. I want my application to work with both the databases..i.e. with Ms-Access 2000 and SQL Server 7.0. I completed my whole application with Ms-Access, everything working fine..then I tried with SQL Server 7.0...by exporting databases from Ms-Access to SQL Server. For most of the parts of my code...which is interacting with DBs thru JDBC worked fine...except for some syntactical changes... but the major problem is with field types of *DATE*. In Ms-Access, the field type is Date/Time - short date and in SQL server it is smalldatetime. E.g. SQL Stmt : Select * From table WHERE (dateField = #2001/01/01#) gives desired results in MS-Access, but when the DB is SQL Server, it gives error. And the same way, SQL Stmt : Select * From table WHERE (dateField = '2001/01/01') gives desired results in SQL Server, but when the DB is Ms-Access, it gives syntax error, saying that data types mismatch in criteria expression. So, how should I deal with these types of things in my code, to make it compatible with Ms-Access and SQL Server as well. Your guidance will be a great help for me. thanks and regards, Chintan.
