XML/SQL bulkloader and datetime

[vbmom]

I've been using the bulkloader to insert to tables in SQL2K, including strings into a datetime column. It's been dandy but the(VB) program that processes text date from various sources is now faced with the possibility of variable date formats. To avoid problems, we're requiring our (VB) program runs in a locale matching the data source, but soon this is impractical.

This program may run on Windows with DMY or MDY format. It will process string dates in either DMY or MDY format. It will use bulkloader to insert those strings into SQL2K datetime columns in either DMY or MDY format. (Can't change the column type.)

There will have to be a parameter that tells the program the date format of the datasource, so we know how to reconstruct the date and prepare to load it into SQL.

If we were using ADO, I understand we can execute SET DATEFORMAT for the connection and insert our dates as strings, or better yet, probably could assign the dates to date vars and let microsoft resolve the date on its way into SQL. Am I right about that, anyone?

However, I'm working with an XML schema and the bulkloader. Right now, the schema defines these dates as string elements. THE BIG QUESTION: can you get the bulkloader to tell SQL "hey, these are dates in the MDY format" so that when "british" SQL sees 1/5/2003 coming in, it stores Jan 5, 2003, not May 1, 2003?

[vbmom]

If anyone cares, the answer to the BIG QUESTION is...

I don't know, BUT

There is a universal datetime format that SQL recognizes can digest from an XSD. The MSDN description of this is

The standard XSD DateTime and Date formats are CCYY-MM-DDThh:mm:ss and CCYY-MM-DD, respectively, because the underlying XSD schema of the DataSet maps the DateTime and Date columns of the database to the DateTime and XSD Date data types.

Instead of a String, you use a dateTime datatype (exactly as capitalized, small "d", big "T") inside the XSD and format your dates CCYY-MM-DDThh:mm:ss in the XML.

Also learned is that SQL server uses the language of the user who connects to the database. But this is irrelevant if you format your dates in the XML as described.

I know you can all rest now. I did.