Date format

In SQL-Server 2000, the supported date format is :
2003-12-30 03:47:34:000

In DB2 and SQL Anywhere Sybase, the date format is :
2003-12-30.03.47.34.00000

I need to import/export data from text files that contain dates in the DB2 format.

Is there a way to change the supported date format in SQL Server 2000 ?
Can I change the default format in the DB-Library ?

No hope but tkx anywaySQL Server default format is actually 2003-12-30 03:47:34.000

We import date from other DBMS as string and then convert it to sql datetime format. In your case it would be as of the following,

select convert(datetime, left(replace(stuff('2003-12-30.03.47.34.00000', 11, 1, ' '), '.', ':'), 19))|||well, you didn't quite answer my question
but it made me think that i could simply
change the Datetime columns of my tables
into Varchar types

Hell with dates !

Thanks !|||If you change your datetime values to varchar, you are just inviting greater problems in the future. Import your data correctly the first time.

You may need to import it first as a string and then convert it (using joejcheng's formula), but you should store your final values in datetime format.

blindman|||The users of my program download files every day and
the program loads them into the database to update their data.
These files come from a DB2 database...

- AND -

Each time they shut down the program,
a file is generated with the latest data updated.

That's why I'm so concerned about the dateformat.|||How does the program load the data? Does it use bulk copy, or does it execute insert statements?

blindman|||You'll need to convert the unload from DB2 and format on the way out...

Yes and varchar is fine

What platform, mainfram, NT, UNIX?

And is ti fixed width (ah, a mainframe canidate) or csv?|||At this moment we're using win98 platform but
next year we'll migrate the prog on winXP.

We use fixed columns files|||We're on Sybase (SQL Anyware) database but
next year we're chaging the database to SQL Server
(boss wants to save money).

I'm studying the impacts and looking for
the smoothest way to change the database-progVB
dialog.|||That's right !

The program imports/exports dates from and to DB2.

So with all you're advices, I'll :

- declare my tables with Char(26) instead of Datetime or VarChar to be sure that I have fixed columns
- even maybe create a user define type named DB2_DateTime with rules on data to be stored in those columns
- use the BlindMan function "CurrentTimestamp" to replace my "Current timestamp" in the old SQL Anywhere queries to store the Now date in the DB2 format
- use bulk insert to import data from a DB2 file (DB2 dates)
- maybe use bcp to export data into a file at DB2 destination

Getting there !
And they say that USA does'nt like France !