473,855 Members | 1,925 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

More date format grief

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
Jul 20 '05
15 43022
Hi,

Simon Brooke wrote:

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


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

Jul 20 '05 #11
Simon Brooke (si***@jasmine. org.uk) writes:
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.


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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #12
Steve Kass <sk***@drew.edu > writes:
[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?
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.

And if there are no
differences from other servers, whether the syslanguages table has not
been modified?


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

A message from our sponsor: This site is now in free fall
Jul 20 '05 #13

"Simon Brooke" <si***@jasmine. org.uk> wrote in message
news:87******** ****@gododdin.i nternal.jasmine .org.uk...
Steve Kass <sk***@drew.edu > writes:
[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?


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'


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

Jul 20 '05 #14
Simon Brooke (si***@jasmine. org.uk) writes:
Steve Kass <sk***@drew.edu > writes:
[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?
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'


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


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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #15
Simon Brooke <si***@jasmine. org.uk> writes:
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.


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!

--
si***@jasmine.o rg.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
watchen das cursorblinken. -- quoted from the jargon file
Jul 20 '05 #16

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
4072
by: ohaya | last post by:
Hi, I'm working with a date string with a format as follows: Sat Dec 25 21:32:59 EST 2004 and I want to compare it to another date string of the format: 7/28/2004
6
31195
by: Dario Di Bella | last post by:
Hi all, we have the following urgent issue affecting our development team. Initially we had one particular workstation that failed executing queries on a DB2 database, raising an invalid date format exception (SQLSTATE=22007). The same queries worked fine on all the other workstations. The date format we want to use is "dd/mm/yyyy".After reinstalling several times the db2 runtime client w/ different options, we found on a technical forum...
8
2253
by: Ishbel Kargar | last post by:
Since upgrading from old laptop (Windows 98) to new laptop (Windows XP), my mail-merge letters are doing strange things with date formats. For instance, my reminder letter for lapsed subs carries the 'expiry' date as a merge field from the database, and this previously was shown as dd/mm/yy (UK format). Now it persists in showing as mm/dd/yy. I've gone to the Windows Control Panel and made sure that my Regional settings show UK format,...
1
2682
by: Neo | last post by:
I use CompareValidator to validate a date. But CompareValidator can only one date format at one time. if the dateorder is "mdy", date format can only be mdy even if date format is ymd. But users of a web application may be around the world and since database can accept many date format, so are there any ways to let CompareValidator accept many data formats at one time? Thanks
1
5093
by: Rotsj | last post by:
Hi, i'm using visual foxpro 9 with a mysql 5 database, for direct access to my database i use navicat. In visual foxpro i've set my date format to dd-mm-yyyy, also i did this in navicat. However when i insert a date into my database i have to insert it with the format yyyy-mm-dd. Is this a setting in mysql or is there another reason? Thanks. Rotsj.
20
35680
by: andreas | last post by:
When I copy a vb.net project using date formats from one PC with a windows date format f.e. dd/mm/yyyy to another PC having a format yy/mm/dd then I get errors. How can I change for a while in the project the date format in vb.code ( not in Windows) and how can I find out which date format the PC Windows is using. Thanks for any response
2
11524
by: syntego | last post by:
We commonly use triggers to log changes to our main tables to historical log tables. In the trigger, we create a concatenated string of the old values by casting them as follows: CAST(O.MYDATE AS CHAR(30)) When directly updating date fields in the main table, the logged value gets saved in the format YYYY-MM-DD as expected.
2
4603
by: Billy | last post by:
This string is supposed to provide all records from an MDB database that match the courier and date specified in the query. I Response.Write the query and I get a date as 1/27/2007. The date format style is exactly the field specification as I see in the MDB Date field in the Courier table. The data for this query exists - both the courier and date that I'm selecting. However, when the file continues processing and the results are...
10
5832
by: ARC | last post by:
Hello all, General question for back-end database that has numerous date fields where the database will be used in regions that put the month first, and regions that do not. Should I save a date format in the table design, such as: mm/dd/yyyy? What I've done for years is to store the date format in date fields, then on the forms, based on their region, I would set the date formats on form_load
0
9754
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10692
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10767
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10375
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9526
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7927
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5754
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4567
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4168
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.