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?