More date format grief

[Simon Brooke]

I'm investigating a bug a customer has reported in our database
abstraction layer, and it's making me very unhappy.

Brief summary:
I have a database abstraction layer which is intended to mediate
between webapps and arbitrary database backends using JDBC. I am very
unwilling indeed to write special-case code for particular
databases. Our code has worked satisfactorily with many databases,
including many instances MS SQLServer 2000 databases using the
com.microsoft.s qlserver.SQLSer verDriver.

However, in this instance, the database won't accept dates. It won't
accept dates in the java.sql.Date.t oString() format (which is the ANSI
SQL 92 format) and it won't accept dates in the ISO8601 format if they
have a zone offset (which in the general case they do) - even if that
zone offset is 'Z'.

I find, by reading on Usenet, that SQL Server doesn't have a default
date format. Furthermore, it doesn't take it's date format from
Windows Regional settings.

So how, for the love of God and Little Fishes, do I persuade a SQL
Server database to accept ANSI SQL 92 dates, permanently, not on a
per-session basis?

--
simon@jasmine.o rg.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

;; all in all you're just another click in the call
;; -- Minke Bouyed

[Steve Kass]

Re: More date format grief

Simon,

I'll agree this is very frustrating, but there is no
easy answer, since there is no international standard for
representation of datetime values. ISO-8601 has a huge
number of options, and SQL Server accepts at least a couple
of the ISO-8601 alternatives.

If you have timezone information in data and want one
product that works with all back ends, then you've probably
got trouble. Not all products support timezones, so your
data will end up with different values on different products.
If you want consistency, either eliminate or convert the
timezone data in your front end, and send every back end you
connect to datetime values as strings of the form
'YYYYMMDD HH:MM:SS' (seconds optional or with fractional
seconds as well).

I thought you could also use '{d YYYY-MM-DD}' and it would
work regardless of settings (unlike 'YYYY-MM-DD' which depends
on date format settings). Not sure about this last bit, though.


Does SQL Server have a default date format? This is several questions:

Q. Does SQL Server display dates as character strings in a
consistent way?
A. SQL Server doesn't display anything. IDEs and front-ends do.

Q. Does SQL Server CAST dates to strings with a consistent format?
A. No. This depends on language settings.

Q. Can SQL Server convert dates to strings with a consistent format?
A. Yes, with CONVERT(varchar ..., <format>) and string functions.

Q. Does SQL Server import every ISO-8601-allowed date correctly?
A. No. It does import a few of them correctly and consistently:
YYYYMMDD HH:MM:SS.fff and YYYY-MM-DDTHH:MM:SS.fff for example.
As far as I know, there is no timezone support.

Q. Does CONVERT(datetim e, ...) with format codes convert
consistently?
A. No. The documentation does not make this clear, but
all the numerical, delimited formats except for the ISO
format with the T depend on the connection's language or
dateformat setting (dateformat overrides language, I believe).


Why doesn't SQL Server consistently convert SQL-92 date strings?
Good question. It converts SQL-92 timestamp (without timezone)
correctly, but not date-only.

It will, if the date format at the time of conversion is mdy,
ymd, or myd, but I don't think that's a great solution.

What's the safest date format to use?
Probably 'YYYYMMDD HH:MM:SS.[fff]', an ISO format, since if
someone truncates it to date-only, it won't break, like the
SQL-92 timestamp form.

-- Steve Kass
-- Drew University
-- Ref: 4BA55F69-6565-4B87-BB19-E223787FDB91




Simon Brooke wrote:[color=blue]
> I'm investigating a bug a customer has reported in our database
> abstraction layer, and it's making me very unhappy.
>
> Brief summary:
> I have a database abstraction layer which is intended to mediate
> between webapps and arbitrary database backends using JDBC. I am very
> unwilling indeed to write special-case code for particular
> databases. Our code has worked satisfactorily with many databases,
> including many instances MS SQLServer 2000 databases using the
> com.microsoft.s qlserver.SQLSer verDriver.
>
> However, in this instance, the database won't accept dates. It won't
> accept dates in the java.sql.Date.t oString() format (which is the ANSI
> SQL 92 format) and it won't accept dates in the ISO8601 format if they
> have a zone offset (which in the general case they do) - even if that
> zone offset is 'Z'.
>
> I find, by reading on Usenet, that SQL Server doesn't have a default
> date format. Furthermore, it doesn't take it's date format from
> Windows Regional settings.
>
> So how, for the love of God and Little Fishes, do I persuade a SQL
> Server database to accept ANSI SQL 92 dates, permanently, not on a
> per-session basis?
>[/color]

[Simon Brooke]

Re: More date format grief

Steve Kass <skass@drew.edu > writes:
[color=blue]
> Simon Brooke wrote:[color=green]
> > I have a database abstraction layer which is intended to mediate
> > between webapps and arbitrary database backends using JDBC. I am very
> > unwilling indeed to write special-case code for particular
> > databases. Our code has worked satisfactorily with many databases,
> > including many instances MS SQLServer 2000 databases using the
> > com.microsoft.s qlserver.SQLSer verDriver.[/color][/color]
[color=blue][color=green]
> > However, in this instance, the database won't accept dates. It won't
> > accept dates in the java.sql.Date.t oString() format (which is the ANSI
> > SQL 92 format) and it won't accept dates in the ISO8601 format if they
> > have a zone offset (which in the general case they do) - even if that
> > zone offset is 'Z'.
> > I find, by reading on Usenet, that SQL Server doesn't have a default
> > date format. Furthermore, it doesn't take it's date format from
> > Windows Regional settings. So how, for the love of God and Little
> > Fishes, do I persuade a SQL
> > Server database to accept ANSI SQL 92 dates, permanently, not on a
> > per-session basis?[/color][/color]
[color=blue]
> Q. Does SQL Server import every ISO-8601-allowed date correctly?
> A. No. It does import a few of them correctly and consistently:
> YYYYMMDD HH:MM:SS.fff and YYYY-MM-DDTHH:MM:SS.fff for example.[/color]

Yes, but, actually, that's not a valid ISO-8601 format, because it
doesn't include a timezone. Furthermore, I don't have the luxury of
being able to generate custom code for every database. Surely it must
be _possible_ to persuade SQL Server to conform to ANSI 92?
[color=blue]
> Why doesn't SQL Server consistently convert SQL-92 date strings?
> Good question. It converts SQL-92 timestamp (without timezone)
> correctly, but not date-only.[/color]

No, it doesn't. That is where all this grief started: we've been
sending that to SQL Server for years and in every other installation
it has worked, but now we have a customer using MS SQL Server 2000 who
is having that fail consistently and repeatedly on one of their boxes
(they have another box running identical software on which it is not
failing, and on our box which we've one everythintg possible to make
identical it doesn't fail). I've done everything I can to find a
difference in setup between the boxes and so far I've failed.

--
simon@jasmine.o rg.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

;; all in all you're just another click in the call
;; -- Minke Bouyed

[Steve Kass]

Re: More date format grief



Simon Brooke wrote:[color=blue]
> Steve Kass <skass@drew.edu > writes:
>
>[color=green]
>>Simon Brooke wrote:
>>[color=darkred]
>>>I have a database abstraction layer which is intended to mediate
>>>between webapps and arbitrary database backends using JDBC. I am very
>>>unwilling indeed to write special-case code for particular
>>>databases. Our code has worked satisfactorily with many databases,
>>>including many instances MS SQLServer 2000 databases using the
>>>com.microsof t.sqlserver.SQL ServerDriver.[/color][/color]
>
>[color=green][color=darkred]
>>>However, in this instance, the database won't accept dates. It won't
>>>accept dates in the java.sql.Date.t oString() format (which is the ANSI
>>>SQL 92 format) and it won't accept dates in the ISO8601 format if they
>>>have a zone offset (which in the general case they do) - even if that
>>>zone offset is 'Z'.
>>>I find, by reading on Usenet, that SQL Server doesn't have a default
>>>date format. Furthermore, it doesn't take it's date format from
>>>Windows Regional settings. So how, for the love of God and Little
>>>Fishes, do I persuade a SQL
>>>Server database to accept ANSI SQL 92 dates, permanently, not on a
>>>per-session basis?[/color][/color]
>
>[color=green]
>>Q. Does SQL Server import every ISO-8601-allowed date correctly?
>>A. No. It does import a few of them correctly and consistently:
>> YYYYMMDD HH:MM:SS.fff and YYYY-MM-DDTHH:MM:SS.fff for example.[/color]
>
>
> Yes, but, actually, that's not a valid ISO-8601 format, because it
> doesn't include a timezone. Furthermore, I don't have the luxury of
> being able to generate custom code for every database. Surely it must
> be _possible_ to persuade SQL Server to conform to ANSI 92?
>[/color]
My reference is ISO8601:2000E (December, 2000), and I don't see
where a timezone is required. Do you have the paragraph number?

Section 5.4 describes point-in-time representations , and says "The
zone designator is empty if use is made of the local time of the
day in accordance...", referring to earlier sections that give
offer hhmm, hh:mm, hhmmss, hh:mm:ss, hh:mm,m, hhmm,m, hh, etc.,
etc., as possible date formats.

It also gives Basic (no hyphens) and extended (with hyphens) formats
for everything, without as far as I can see mandating one or the
other. It would be nice if SQL Server understood them all, but it
does understand the one with hyphens and a T (ISO allows the T to be
omitted if no ambiguity results, though I couldn't see where
any would regarding other ISO formats - probably missed something
crazy like week numbers in BC years that used a T.)

SQL Server also understands the one with no hyphens or T.
It looks ok in ISO to omit date separators but include time
separators.[color=blue]
>[color=green]
>>Why doesn't SQL Server consistently convert SQL-92 date strings?
>>Good question. It converts SQL-92 timestamp (without timezone)
>>correctly, but not date-only.[/color]
>
>
> No, it doesn't. That is where all this grief started: we've been
> sending that to SQL Server for years and in every other installation
> it has worked, but now we have a customer using MS SQL Server 2000 who
> is having that fail consistently and repeatedly on one of their boxes
> (they have another box running identical software on which it is not
> failing, and on our box which we've one everythintg possible to make
> identical it doesn't fail). I've done everything I can to find a
> difference in setup between the boxes and so far I've failed.
>[/color]

My slip. SQL Server doesn't understand SQL-92

TIMESTAMP '2003-02-22 23:34:43.123' at all, as in

CAST(TIMESTAMP '2003-02-22 23:34:43.123' as DATETIME)

but I doubt you are construction CAST(TIMESTAMP ...
expressions. SQL Server uses
{ts '1996-12-19 11:11:11.000'} to represent
a timestamp literal, and interprets it unambiguously,
as far as I know, as it does the date literal format of
{d '1996-12-19'}

Without the {ts ... }, these strings alone, like all
numeric delimited date formats, when implicitely
converted to dates follow the relative positions of
d and m in the dateformat setting in effect implicit
from the language selection or explicitly set.

set dateformat dmy
go
declare @d datetime
set @d = {ts '1996-12-19 11:11:11.000'}
select @d
go
declare @d datetime
set @d = {d '1996-12-19'}
select @d
go
declare @d datetime
set @d = '1996-12-19 11:11:11.000'
select @d
go
declare @d datetime
set @d = '1996-12-19'
select @d

I don't know why you would have trouble with this if the
settings were right, but maybe there's some driver parameter
buried in the registry, or some other setting that's not
obvious. Perhaps someone wanted us_english but dmy, and
got the bright idea of modifying the syslanguages table!

Does that bum server error out on this??

set dateformat dmy
declare @d datetime
set @d = '2003-02-19'


If the server is installed as us_english, and no one
has changed the dateformat setting or modified syslanguages,
I think it should work and might be a case for product
support. On the other hand, I wouldn't
want a product that depended on the language of installation.

SK

[Ben McIntyre]

Re: More date format grief

Simon

This may be useless, but you don't seem to have a lot SQL Server
2000-specific info.
SQL server interprets data based on a 'collation' which is set at
install time, and can be overridden manually in an SQL statement.
To find the default collation for the database, the user will need to
right click on the SQL server instance in Enterprise Manager and
choose 'properties'. The default collation is displayed as part of
the basinc database information.
This can only be changed if the databases on the server are rebuilt.
Some information below from the SQL server 'man pages'

I haven't had the problems you describe, but if there is a
configuration difference between two installs, which is causing the
problem you describe, this is likely to be it.
From what I understand, you are saying that one installation processes
the dates OK, and the other does not. So SQL Server 2000 will do the
job, it is just not configured correctly on one of the servers.

good luck

Ben McIntyre

<snip>
----------------------------------------------------------------------------

Collation Options for International Support
In Microsoft® SQL Server™ 2000, it is not required to separately
specify code page and sort order for character data, and the collation
used for Unicode data. Instead, specify the collation name and sorting
rules to use. The term, collation, refers to a set of rules that
determine how data is sorted and compared. Character data is sorted
using rules that define the correct character sequence, with options
for specifying case-sensitivity, accent marks, kana character types,
and character width. Microsoft SQL Server 2000 collations include
these groupings:

Windows collations
Windows collations define rules for storing character data based on
the rules defined for an associated Windows locale. The base Windows
collation rules specify which alphabet or language is used when
dictionary sorting is applied, as well as the code page used to store
non-Unicode character data. For more information, see Collations.

SQL collations
SQL collations are provided for compatibility with sort orders in
earlier versions of Microsoft SQL Server. For more information, see
Using SQL Collations.

Changing Collations After Setup
When you set up SQL Server 2000, it is important to use the correct
collation settings. You can change collation settings after running
Setup, but you must rebuild the databases and reload the data. It is
recommended that you develop a standard within your organization for
these options. Many server-to-server activities can fail if the
collation settings are not consistent across servers.


-----------------------------------------------------------------------
How To


How to rebuild the master database (Rebuild Master utility)
To rebuild the master database

Shutdown Microsoft® SQL Server™ 2000, and then run Rebuildm.exe. This
is located in the Program Files\Microsoft SQL Server\80\Tools \Binn
directory.


In the Rebuild Master dialog box, click Browse.


In the Browse for Folder dialog box, select the \Data folder on the
SQL Server 2000 compact disc or in the shared network directory from
which SQL Server 2000 was installed, and then click OK.


Click Settings. In the Collation Settings dialog box, verify or change
settings used for the master database and all other databases.
Initially, the default collation settings are shown, but these may not
match the collation selected during setup. You can select the same
settings used during setup or select new collation settings. When
done, click OK.

In the Rebuild Master dialog box, click Rebuild to start the process.
The Rebuild Master utility reinstalls the master database.



Note To continue, you may need to stop a server that is running.
</snip>

[Simon Brooke]

Re: More date format grief

ben_spam@mailci ty.com (Ben McIntyre) writes:
[color=blue]
> Simon
>
> This may be useless, but you don't seem to have a lot SQL Server
> 2000-specific info.
> SQL server interprets data based on a 'collation' which is set at
> install time, and can be overridden manually in an SQL statement.
> To find the default collation for the database, the user will need to
> right click on the SQL server instance in Enterprise Manager and
> choose 'properties'. The default collation is displayed as part of
> the basinc database information.
> This can only be changed if the databases on the server are rebuilt.
> Some information below from the SQL server 'man pages'
>
> I haven't had the problems you describe, but if there is a
> configuration difference between two installs, which is causing the
> problem you describe, this is likely to be it.
> From what I understand, you are saying that one installation processes
> the dates OK, and the other does not. So SQL Server 2000 will do the
> job, it is just not configured correctly on one of the servers.[/color]

OK, thanks for that. The collation sequence on the database on our
test box (which _does_ work) is SQL_Latin1_Gene ral_CP1_CI_AS. My
customer isn't yet in this morning so I can't ring and check what his
is.

I'll post a resolution once I get it sorted in case anyone searches
google any time in the future for a similar problem.

Just so that, in future, I know the general solution, which 'bit' of the
collation name is it which affects date sequencing? I mean, for
example, if in future I have a similar problem with a customer not in
the Latin1 area, what collation advice to I offer?

Cheers

Simon

--
simon@jasmine.o rg.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

[ This mind intentionally left blank ]

[Tibor Karaszi]

Re: More date format grief

> Just so that, in future, I know the general solution, which 'bit' of the[color=blue]
> collation name is it which affects date sequencing?[/color]

You mean datetime datattype? That is not affected by collations at all. If this is your problem, you
might want to post the problem at hand again (It has been "aged out").

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsof t.public.sqlser ver


"Simon Brooke" <simon@jasmine. org.uk> wrote in message
news:87n0dtot6r .fsf@gododdin.i nternal.jasmine .org.uk...[color=blue]
> ben_spam@mailci ty.com (Ben McIntyre) writes:
>[color=green]
> > Simon
> >
> > This may be useless, but you don't seem to have a lot SQL Server
> > 2000-specific info.
> > SQL server interprets data based on a 'collation' which is set at
> > install time, and can be overridden manually in an SQL statement.
> > To find the default collation for the database, the user will need to
> > right click on the SQL server instance in Enterprise Manager and
> > choose 'properties'. The default collation is displayed as part of
> > the basinc database information.
> > This can only be changed if the databases on the server are rebuilt.
> > Some information below from the SQL server 'man pages'
> >
> > I haven't had the problems you describe, but if there is a
> > configuration difference between two installs, which is causing the
> > problem you describe, this is likely to be it.
> > From what I understand, you are saying that one installation processes
> > the dates OK, and the other does not. So SQL Server 2000 will do the
> > job, it is just not configured correctly on one of the servers.[/color]
>
> OK, thanks for that. The collation sequence on the database on our
> test box (which _does_ work) is SQL_Latin1_Gene ral_CP1_CI_AS. My
> customer isn't yet in this morning so I can't ring and check what his
> is.
>
> I'll post a resolution once I get it sorted in case anyone searches
> google any time in the future for a similar problem.
>
> Just so that, in future, I know the general solution, which 'bit' of the
> collation name is it which affects date sequencing? I mean, for
> example, if in future I have a similar problem with a customer not in
> the Latin1 area, what collation advice to I offer?
>
> Cheers
>
> Simon
>
> --
> simon@jasmine.o rg.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/
>
> [ This mind intentionally left blank ][/color]

[Erland Sommarskog]

Re: More date format grief

Ben McIntyre (ben_spam@mailc ity.com) writes:[color=blue]
> SQL server interprets data based on a 'collation' which is set at
> install time, and can be overridden manually in an SQL statement.
> To find the default collation for the database, the user will need to
> right click on the SQL server instance in Enterprise Manager and
> choose 'properties'. The default collation is displayed as part of
> the basinc database information.[/color]

Collation apply to string columns, not to datetime columns.

The two commands that affect how strings is interpreted are
SET DATEFORMAT and SET LANGUAGE.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Simon Brooke]

Re: More date format grief

"Tibor Karaszi" <tibor.please_r eply_to_public_ forum.karaszi@c ornerstone.se> writes:
[color=blue][color=green]
> > Just so that, in future, I know the general solution, which 'bit' of the
> > collation name is it which affects date sequencing?[/color]
>
> You mean datetime datattype? That is not affected by collations at all. If this is your problem, you
> might want to post the problem at hand again (It has been "aged out").[/color]

Ouch, I feared that.

Briefly, I have a piece of cross-platform Java code which is used in
production environments against at least five different database
backends. Many installations use SQL Server and have been running
reliably since 1998, and several installations use SQL Server 2000
with the com.microsoft.s qlserver.SqlSer verDriver satisfactorily.

Yesterday, one of our customers reported a problem and on
investigation we found that their (new) installation wasn't accepting
dates properly. It would not accept the date 28th August 2003 at all,
and when (at my suggestion) they tried 4th August 2003, they got back
8th April 2003, which showed we had a date format problem.

The code asks the database for the column type of each column and
formats the data appropriately; because SQL Server doesn't support
date fields it responds that the date/time fields which on other
databases would be date fields are of type java.sql.Types. TIMESTAMP,
and consequently my code formats them as ANSI 92 timestamp format,
namely

yyyy-mm-dd hh:mm:ss.ffffff fff

As I say, we've got loads of SQL Server installations which are
working quite happily with this. We've got exactly one which isn't. We
haven't been able to reproduce the bug on our test machine. We haven't
been able to identify any difference in configuration between the
machine that doesn't work and ones which do.

I'm very unwilling indeed to write special purpose code for different
database backends as it will lead to maintenance problems (I know this,
because we have one special purpose hack to work around an Oracle
misfeature). I'd like to resolve this problem if I can by specifying
the required SQL Server configuration.

We've today sent the customer a patch which dumps and deletes the
database and recreates it with the collation which we have on our
test box but it sounds from what you are saying as though this is
unlikely to work.

Can you offer any other suggestions?

Many thanks

Simon, not much impressed by Microsoft at the best of times.

--
simon@jasmine.o rg.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

;; Friends don't send friends HTML formatted emails.

[Steve Kass]

Re: More date format grief

[microsoft.publi c.sqlserver.set up removed - can't send to two mail
servers at once, unfortunately.. .]

Simon,

Can you see what

DBCC USEROPTIONS

returns on the connection that is failing? And if there are no
differences from other servers, whether the syslanguages table has not
been modified?

SK

Simon Brooke wrote:[color=blue]
> "Tibor Karaszi" <tibor.please_r eply_to_public_ forum.karaszi@c ornerstone.se> writes:
>
>[color=green][color=darkred]
>>>Just so that, in future, I know the general solution, which 'bit' of the
>>>collation name is it which affects date sequencing?[/color]
>>
>>You mean datetime datattype? That is not affected by collations at all. If this is your problem, you
>>might want to post the problem at hand again (It has been "aged out").[/color]
>
>
> Ouch, I feared that.
>
> Briefly, I have a piece of cross-platform Java code which is used in
> production environments against at least five different database
> backends. Many installations use SQL Server and have been running
> reliably since 1998, and several installations use SQL Server 2000
> with the com.microsoft.s qlserver.SqlSer verDriver satisfactorily.
>
> Yesterday, one of our customers reported a problem and on
> investigation we found that their (new) installation wasn't accepting
> dates properly. It would not accept the date 28th August 2003 at all,
> and when (at my suggestion) they tried 4th August 2003, they got back
> 8th April 2003, which showed we had a date format problem.
>
> The code asks the database for the column type of each column and
> formats the data appropriately; because SQL Server doesn't support
> date fields it responds that the date/time fields which on other
> databases would be date fields are of type java.sql.Types. TIMESTAMP,
> and consequently my code formats them as ANSI 92 timestamp format,
> namely
>
> yyyy-mm-dd hh:mm:ss.ffffff fff
>
> As I say, we've got loads of SQL Server installations which are
> working quite happily with this. We've got exactly one which isn't. We
> haven't been able to reproduce the bug on our test machine. We haven't
> been able to identify any difference in configuration between the
> machine that doesn't work and ones which do.
>
> I'm very unwilling indeed to write special purpose code for different
> database backends as it will lead to maintenance problems (I know this,
> because we have one special purpose hack to work around an Oracle
> misfeature). I'd like to resolve this problem if I can by specifying
> the required SQL Server configuration.
>
> We've today sent the customer a patch which dumps and deletes the
> database and recreates it with the collation which we have on our
> test box but it sounds from what you are saying as though this is
> unlikely to work.
>
> Can you offer any other suggestions?
>
> Many thanks
>
> Simon, not much impressed by Microsoft at the best of times.
>[/color]

[Luis Neves]

Re: More date format grief

Hi,

Simon Brooke wrote:

[...]
[color=blue]
> So how, for the love of God and Little Fishes, do I persuade a SQL
> Server database to accept ANSI SQL 92 dates, permanently, not on a
> per-session basis?[/color]

Back when I was a ASP programmer the way do deal with this was to format the
date like "dd-MMM-yyyy", where "MMM" is the three-letter abbreviation of
the month.
This works because the Database understands how to read the dd-MMM-yyyy
format. this behaviour is not particular to SQL Server, I just tried it in
JDBC/PostgreSQL (don't have access to MSSQL right now) and it works
also.... I would be surprised if it didn't worked in JDBC/MSSQL.

CREATE TABLE public.tbl_test
(
datefield date
) ;

********** JAVA *************
Connection c = getConnection() ;
PreparedStateme nt statement = c.prepareStatem ent("INSERT INTO
tbl_test(datefi eld) VALUES (?)");

statement.setOb ject(1, "10-Sep-2003");
statement.execu te();
statement.clear Parameters();
statement.close ();
*************** *************** ****

SELECT * FROM tbl_test;
datefield
------------
2003-09-10
(1 row)


There is a catch though, you have to be carefull with what you write as
"MMM", if the DB server is configured in other language other than English
the month abbreviation must comply to that language.

I hope this helps.

Regards,
Luis Neves

[Erland Sommarskog]

Re: More date format grief

Simon Brooke (simon@jasmine. org.uk) writes:[color=blue]
> The code asks the database for the column type of each column and
> formats the data appropriately; because SQL Server doesn't support
> date fields it responds that the date/time fields which on other
> databases would be date fields are of type java.sql.Types. TIMESTAMP,
> and consequently my code formats them as ANSI 92 timestamp format,
> namely
>
> yyyy-mm-dd hh:mm:ss.ffffff fff
>
> As I say, we've got loads of SQL Server installations which are
> working quite happily with this. We've got exactly one which isn't.[/color]

Which smells no bit of luck, given that you post with a UK address.

Try this script:

SET DATEFORMAT dmy
SELECT convert(datetim e, '2002-12-18 12:12:12.000') -- Fails
go
SET DATEFORMAT mdy
SELECT convert(datetim e, '2002-12-18 12:12:12.000') -- Passes
go
SET LANGUAGE British
SELECT convert(datetim e, '2002-12-18 12:12:12.000') -- Fails
go
SET LANGUAGE us_english
SELECT convert(datetim e, '2002-12-18 12:12:12.000') -- Passes
go

The dateformat setting is a pure run-time setting. However, changing
language also changes the dateformat setting. And the language can
be set by a default on a login with sp_defaultlangu age. Finally, there
is a server configuration option that determines the default language
for new logins.

If your java app logs in with a certain login, you can probably mandate
that the default language of this login should be one that has a dateformat
of ymd or mdy, for instance Swedish.

If you can't mandate the language, it seems that you need to adapt your
app how much you hate it.

I should add that this problem appears because you are sending down
raw SQL statements to SQL Server, rather than parameterized queries
or RPC calls to stored procedures. If you do this, the client library
will handle the date format and pass SQL Server a binary value which
is not subject to settings. Whether this is possible to do in Java, I
have no idea, but client libraries such as ODBC and ADO supports it,
so why not JDBC?

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Simon Brooke]

Re: More date format grief

Steve Kass <skass@drew.edu > writes:
[color=blue]
> [microsoft.publi c.sqlserver.set up removed - can't send to two mail
> servers at once, unfortunately.. .]
>
> Simon,
>
> Can you see what
>
> DBCC USEROPTIONS
>
> returns on the connection that is failing?[/color]

I'm sorry, how do I do this? I'm not by any means a SQL Server
expert. I tried it in query analyzer and got:

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sbcc'

When I try it over the JDBC connection I get:
DBCC USEROPTIONS
SQL Error
java.sql.SQLExc eption: [Microsoft][SQLServer 2000 Driver for JDBC]No rows affected.
DBCC USEROPTIONS;

SQL Error
java.sql.SQLExc eption: [Microsoft][SQLServer 2000 Driver for JDBC]Syntax error at token 0, line 0 offset 0.

[color=blue]
> And if there are no
> differences from other servers, whether the syslanguages table has not
> been modified?[/color]

There does not appear to be syslanguages table in the database. There
are plenty of other 'dbo.sysxxx' tables, but not syslanguages. This is
SQL Server 2000.


--
simon@jasmine.o rg.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

A message from our sponsor: This site is now in free fall

[Greg D. Moore \(Strider\)]

Re: More date format grief


"Simon Brooke" <simon@jasmine. org.uk> wrote in message
news:878yp38qg1 .fsf@gododdin.i nternal.jasmine .org.uk...[color=blue]
> Steve Kass <skass@drew.edu > writes:
>[color=green]
> > [microsoft.publi c.sqlserver.set up removed - can't send to two mail
> > servers at once, unfortunately.. .]
> >
> > Simon,
> >
> > Can you see what
> >
> > DBCC USEROPTIONS
> >
> > returns on the connection that is failing?[/color]
>
> I'm sorry, how do I do this? I'm not by any means a SQL Server
> expert. I tried it in query analyzer and got:
>
> Server: Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'sbcc'[/color]

Umm, here is simply a typo. This will work in Query Analyzter.

[Erland Sommarskog]

Re: More date format grief

Simon Brooke (simon@jasmine. org.uk) writes:[color=blue]
> Steve Kass <skass@drew.edu > writes:[color=green]
>> [microsoft.publi c.sqlserver.set up removed - can't send to two mail
>> servers at once, unfortunately.. .]
>>
>> Simon,
>>
>> Can you see what
>>
>> DBCC USEROPTIONS
>>
>> returns on the connection that is failing?[/color]
>
> I'm sorry, how do I do this? I'm not by any means a SQL Server
> expert. I tried it in query analyzer and got:
>
> Server: Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'sbcc'[/color]

As Greg Strider pointed out, you gave a typo, and I don't want to
be sarcastic or anything, but double-checking what you typed, before
you ask for help, may increase your effectivenesss.
[color=blue]
> When I try it over the JDBC connection I get:
> DBCC USEROPTIONS
> SQL Error
> java.sql.SQLExc eption: [Microsoft][SQLServer 2000 Driver for JDBC]No rows
> affected.
> DBCC USEROPTIONS;
>
> SQL Error
> java.sql.SQLExc eption: [Microsoft][SQLServer 2000 Driver for JDBC]Syntax
> error at token 0, line 0 offset 0.[/color]

Some DBCC commands produces their output as messages, which could
confuse some drivers. However, USEROPTIONS always produce a result set.
Maybe the JDBC driver is too smart for its own good and performs its
own parsing, and don't recognize the command. Not knowing about
JDBC I cannot really help.
[color=blue]
> There does not appear to be syslanguages table in the database. There
> are plenty of other 'dbo.sysxxx' tables, but not syslanguages. This is
> SQL Server 2000.[/color]

syslanguages is in master. I would hold it as unlikely that someone
has changed syslanguages.

In any case, I seem to recall that I tried to explained exactly what
was going on a couple of days ago. Did you see that post?

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp