More date format grief -- sql server (alternate)

Steve Kass
8/28/2003 7:37:18 PM

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(datetime, ...) 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

[quoted text, click to view]

ben_spam NO[at]SPAM mailcity.com
8/28/2003 9:36:41 PM

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.

Simon Brooke
8/28/2003 10:35:02 PM

Steve Kass <skass@drew.edu> writes:

[quoted text, click to view]

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?

[quoted text, click to view]

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.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

;; all in all you're just another click in the call

Steve Kass
8/29/2003 1:05:20 AM

[quoted text, click to view]
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.
[quoted text, click to view]

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

Simon Brooke
8/29/2003 7:05:03 AM

ben_spam@mailcity.com (Ben McIntyre) writes:

[quoted text, click to view]

OK, thanks for that. The collation sequence on the database on our
test box (which _does_ work) is SQL_Latin1_General_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.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

Tibor Karaszi
8/29/2003 2:29:55 PM

[quoted text, click to view]

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=microsoft.public.sqlserver

[quoted text, click to view]

Erland Sommarskog
8/29/2003 9:37:13 PM

Ben McIntyre (ben_spam@mailcity.com) writes:
[quoted text, click to view]

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

Simon Brooke
8/29/2003 10:05:03 PM

"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se> writes:

[quoted text, click to view]

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.sqlserver.SqlServerDriver 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.fffffffff

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.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

Steve Kass
8/30/2003 4:06:51 AM

[microsoft.public.sqlserver.setup 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

[quoted text, click to view]

Luis Neves
8/30/2003 6:22:50 PM

Hi,

[quoted text, click to view]

[...]

[quoted text, click to view]

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();
PreparedStatement statement = c.prepareStatement("INSERT INTO
tbl_test(datefield) VALUES (?)");

statement.setObject(1, "10-Sep-2003");
statement.execute();
statement.clearParameters();
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
8/30/2003 7:24:28 PM

Simon Brooke (simon@jasmine.org.uk) writes:
[quoted text, click to view]

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

Try this script:

SET DATEFORMAT dmy
SELECT convert(datetime, '2002-12-18 12:12:12.000') -- Fails
go
SET DATEFORMAT mdy
SELECT convert(datetime, '2002-12-18 12:12:12.000') -- Passes
go
SET LANGUAGE British
SELECT convert(datetime, '2002-12-18 12:12:12.000') -- Fails
go
SET LANGUAGE us_english
SELECT convert(datetime, '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_defaultlanguage. 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

Simon Brooke
9/5/2003 11:05:02 AM

Steve Kass <skass@drew.edu> writes:

[quoted text, click to view]

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.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]No rows affected.
DBCC USEROPTIONS;

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

[quoted text, click to view]

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.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

Greg D. Moore (Strider)
9/5/2003 1:29:58 PM

[quoted text, click to view]

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

Erland Sommarskog
9/5/2003 10:01:19 PM

Simon Brooke (simon@jasmine.org.uk) writes:
[quoted text, click to view]

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.

[quoted text, click to view]

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.

[quoted text, click to view]

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

Simon Brooke
10/6/2003 5:05:03 PM

Simon Brooke <simon@jasmine.org.uk> writes:

[quoted text, click to view]

OK, just for the record here is the resolution of this issue.

What we found was that on the servers which worked, the user logins
used by the application had language set to 'English', and not either
'US English' or 'British English'. We set the language on the server
that didn't work to 'English', and it worked.

Many thanks to everyone who helped!

--
simon@jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/
Das Internet is nicht fuer gefingerclicken und giffengrabben... Ist
nicht fuer gewerken bei das dumpkopfen. Das mausklicken sichtseeren
keepen das bandwit-spewin hans in das pockets muss; relaxen und

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

sql server (alternate) : More date format grief