Date Format Issue while uploading to SQL Server Management Studio 2008 using DTW in SAP Business One Application

[amiruddin_ayaz]

HI,

I am working on SAP B1 and have created an excel template using Data Transfer Workbench, so i could bulk upload the data on SQL Server Management Studio. I'm facing trouble while adding an entry/row which has dates in it.

The UDT's structure for that column is set to Date, I've also set the date's column data format to 'ddmmyy' in excel so that the data could be uploaded successfully.

Now the issue comes in some data values, for example:

begdate | enddate

311299  | 311200

After using DTW, and then quering the SAP B1 table in the sql server gives:

              begdate                    |             enddate

1999-12-31 00:00:00.000   | 2000-12-31 00:00:00.000

Though the true values should be:

              begdate                    |             enddate

9999-12-31 00:00:00.000   | 1900-12-31 00:00:00.000

Can someone please help me solving this problem? Appreciate your help

Regards,

Amiruddin Ayaz

[srinivas_kanavena]

Hi Amiruddin Ayaz,

Please maintain BEGDAT and ENDDAT in below format in Excel.

Try below format

BEGDAT  | ENDDAT

31121900| 31129999

Hope this helps.

Reward if helpful.

Regards,

Srinivas K

[edy_simon]

Hi,

Pass in the values as 'yyyyMMdd' format.

Regards

Edy

[amiruddin_ayaz]

It gives an error

Regards,

Amiruddin Ayaz

[srinivas_kanavena]

Hi Amiruddin Ayaz,

Please maintain BEGDAT and ENDDAT in below format in Excel.

BEGDAT  | ENDDAT

31129999 | 31121900 

OR

BEGDAT  | ENDDAT

99991231  | 19001231

Hope this helps.

Reward if helpful.

Regards,

Srinivas K

[amiruddin_ayaz]

The first approach uploads '1999-12-31 00:00:00.000'  in the begdate column whereas my requirement is to upload '9999-12-31 00:00:00.000'

The second approach gives an error

Regards,

Amiruddin Ayaz

[Johan_H]

Hi,

Apparently your database stores the dates in the format 'yyyy-MM-dd'.

Please give that format a try, so SQL Server does not have to do any conversions.

Regards,

Johan