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?

--
si***@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]

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:

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 Brooke]

Steve Kass <sk***@drew.edu > writes:

Simon Brooke wrote:

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?

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.
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?
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.

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.

--
si***@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]

Simon Brooke wrote:

Steve Kass <sk***@drew.edu > writes:

Simon Brooke wrote:

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.
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?

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.

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?

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.

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.

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.

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]

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]

be******@mailci ty.com (Ben McIntyre) writes:

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.

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

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

[ This mind intentionally left blank ]

[Tibor Karaszi]

> Just so that, in future, I know the general solution, which 'bit' of the

collation name is it which affects date sequencing?
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" <si***@jasmine. org.uk> wrote in message
news:87******** ****@gododdin.i nternal.jasmine .org.uk... be******@mailci ty.com (Ben McIntyre) writes:

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.

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

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

[ This mind intentionally left blank ]

[Erland Sommarskog]

Ben McIntyre (be******@mailc ity.com) writes:

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.

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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Simon Brooke]

"Tibor Karaszi" <ti************ *************** *************@c ornerstone.se> writes:

Just so that, in future, I know the general solution, which 'bit' of the
collation name is it which affects date sequencing?

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").

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.

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

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

[Steve Kass]

[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:

"Tibor Karaszi" <ti************ *************** *************@c ornerstone.se> writes:

Just so that, in future, I know the general solution, which 'bit' of the
collation name is it which affects date sequencing?

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").

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.