Importing dates into table

[eo]

Hi

I have xls spreadsheets that have been designed in a UK date format environment (i.e. dd/mm/yyyy).
I SQL table has been created for import of such spreadsheets, in a US date format environment (i.e. mm/dd/yyyy).
When the spreadsheet is saved on to the US server, the dates in the spreadsheet is automatically converted to US format:
It will take what is meant to be 31 Mar 2004 i.e. 31/03/2004 incorrectly on its face value (which is rubbish as there is no 31st month!). But it will take what is meant to be 04 Feb 2004 on its face value of 04/02/2004 but obviously interpret it to mean 2 Apr 2004.
When the spreadsheet is imported into the SQL table it will not import the 31/03/2004 as it does not understand this date, and it will import the 04/02/2004, but it implies the wrong date!!!

Any suggestion how this can be solved?

EO
Hertfordshire, England

 

[manjarikosaraju]

Hi EO,

Create a temporary table with exactly the same datatype for all other columns in the original table excpet for date coulmn. For date column change the datatype to
varchar(10). Then import the spreadsheet saved under US format into this temporary table.

Then copy the records from temporary table to the actual table you need to save the results in, using
convert(datetime, datefield, 103).

Hope this helps.

-Manjari

 

[eo]

The tables have been created so in theory its just a case of changing from datetime to varchar(10). Is there therefore a way I can copy the design of the existing table as a temp table so save me doing all the design work again?

EO
Hertfordshire, England

 

[manjarikosaraju]

You need to change the datatype to varchar(10) to get the dates as it is in the excel sheet. Then you need to convert it into datetime format so that it has the right month and day.

To create a temp table with the same structure as original table do the following:

1. In SQL query analyser, Open the object browser.
2. In the corresponding database -> UserTables -> Right
3. Click on the Table Name.
Select Script Object to New Window As -> Create option

4. It generates the structure of the table with its datatype. Now change the name of the table to temporary table and change the datatype of datecolumn to varchar(10).

5.Then Import the excel spreadsheet into this temporary table.

6. Insert the data from this temporary table into the original table using convert(datetime, datecolumn, 103)

Please let us know if you need more help.

-Manjari.