January 18, 2011 at 4:59 am
Hi,
I'm trying to import a date with a timestamp into SQL2008 - and it doesn’t work.
What would you recommend for the best format to import as, the format is 23/06/2009 23:55
thank you,
Ant
January 18, 2011 at 5:48 am
anthony.hart (1/18/2011)
Hi,I'm trying to import a date with a timestamp into SQL2008 - and it doesn’t work.
What would you recommend for the best format to import as, the format is 23/06/2009 23:55
How are you trying to import the date?
What are the errors that you receive?
IMO, the best data format is the following (ISO8601):
yyyy-mm-ddThh:mi:ss.nnnnnnn (if you are using the regular datetime, you won't have this much precision)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 18, 2011 at 6:08 am
I'm using the import function under the DB table - so the only options i have are just the standard Data Types.
January 18, 2011 at 6:15 am
anthony.hart (1/18/2011)
I'm using the import function under the DB table - so the only options i have are just the standard Data Types.
Are you importing from an excel file, a flat file?
Do you receive any errors?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 18, 2011 at 6:17 am
From a flat file.
There are no error messages, it just changes the type to string, and if i change the type to date, it cuts off the timestamp at the end
January 18, 2011 at 6:20 am
anthony.hart (1/18/2011)
From a flat file.There are no error messages, it just changes the type to string, and if i change the type to date, it cuts off the timestamp at the end
Ah ok. I see 🙂
The Date data type doesn't have a time portion. You should select the datetime or datetime2 data type.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 20, 2011 at 3:29 am
Hi,
I have an error when importing:
- Copying to [dbo].[topfinance3] (Error)
Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid date format".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid date format".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid date format".
(SQL Server Import and Export Wizard)
Error 0xc020901c: Data Flow Task 1: There was an error with input column "Consolidation date" (192) on input "Destination Input" (151). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (151)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (151)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - topfinance3" (138) failed with error code 0xC0209029 while processing input "Destination Input" (151). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
January 20, 2011 at 3:32 am
Make sure that the date format used in Excel is compatible with the one used in SQL Server.
For example: mm/dd/yyyy versus dd/mm/yyyy
When it is the first one, 20/01/2011 will give an overflow error.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply