SQL 6.5 migrating to SQL 2000

[red1028]

Hi,

I've got a database that needs to migrate from SQL 6.5 to SQL 2000. I'm
having problems with Datetime data types. In SQL 6.5 the Date is shown as
'Mar 23 2004 12:00AM' but when I've copied the database across to SQL 2000,
although it's still a Datetime data type, it is displayed as '2004-03-23
00:00:00.000'. Unfortunately it means Stored Procedures called through a
front end Visual Basic application do not work.

Colleagues have suggested checking the collation, which I did and it's the
same as the older 6.5 database. Is there another property that needs to be
changed to allow the new Datetime dates to be shown in the desired format?
FYI I have Systems Administrator privileges on the older 6.5 database but
not on the new 2000 one.

I'm hoping there must be an flag/option I can change. Any help would be
gratefully appreciated.

Kind regards

Rich

[Simon Hayes]

MSSQL stores data internally as binary values - it's entirely up to the
client to decide how to display them. It's not really clear from the
above where you're seeing that date format (Query Analyzer? your own
application?), but you should be using a 'neutral' format which MSSQL
will interpret correctly. In particular, YYYYMMDD will always work
(assuming you don't need the time part), but the two formats you
mentioned may not, depending on the MSSQL installation language, client
regional settings etc.

The information here should help to clarify how MSSQL handles dates:

http://www.karaszi.com/sqlserver/info_datetime.asp

If this doesn't help, I suggest you post some more details of exactly
how you call your stored procedures, how you're passing the datetime to
them, and some sample code to illustrate your problem. It would also be
useful to know what "do not work" means - do you get an error, no error
but unexpected results, silent failure etc.

Simon