Converting string to date format

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Converting string to date format

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-06-12 : 15:49:14

I am trying to import a txt file that has a string for it's date. How do I change it over in the import wizard I've tried going to advance then changing from string to date or date time but I get an error.

wwphx
Starting Member

1 Post

Posted - 2008-06-13 : 11:04:25

Are you using DTS? You can do date transformations very easily there, just yesterday I converted a string that was mmddyyyyhhmmss to smalldatetime with no difficulty.

Check BOL for "Date Time String Transformation".

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-13 : 11:16:18

What is the source format of the date?

Madhivanan

Failing to plan is Planning to fail

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-06-13 : 11:47:39

Yes I'm using DTS.

Source format is 20061201

Do I change this in DTS?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-13 : 11:51:57

quote:
Originally posted by JJ297

Yes I'm using DTS.

Source format is 20061201

Do I change this in DTS?

Did you try converting to DATETIME type?
Did you get any error?

Madhivanan

Failing to plan is Planning to fail

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-06-13 : 12:46:08

I'm getting this:

Messages
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 2" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "Column 2" (18)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "Column 2" (18)" 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 0xc0202092: Data Flow Task: An error occurred while processing file "W:\Offsets\FullDownload\test1.txt" on data row 1.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - test1_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 12:51:23

http://www.eggheadcafe.com/forumarchives/SQLServerdts/Oct2005/post24522148.asp

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-06-13 : 12:56:52

I'm using SQL Server Management Studio - SQL Server 2005. Where is the transformation tab? Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 13:04:03

quote:
Originally posted by JJ297

I'm using SQL Server Management Studio - SQL Server 2005. Where is the transformation tab? Thanks

But you told in earlier post that you're using DTS?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-06-13 : 13:12:20

I'm sorry new to 2005 and still using 2000 lingo. Is this the right forum for IIS Management Studio?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 13:13:44

quote:
Originally posted by JJ297

I'm sorry new to 2005 and still using 2000 lingo. Is this the right forum for IIS Management Studio?

So are you using Export/Import wizard in SSMS?

cycledude
Starting Member

9 Posts

Posted - 2008-06-13 : 13:50:28

The SQL Server Import Wizard only gives you limited conversion capabilities. Visual Studio will allow a SSIS Package to be created via the Integration Services Project type. One of the steps on the Control Flow tab can be a Data Flow Task (object) from the toolbar. When this object is added, a second tab appears showing the steps for the Data Flow Task. Normally, you would use a Source object, a Script Component object, and a Destination object. Right-click Script Component and you will find the Script Transformation Editor window. Choose the "Script" option on the navbar and then the Design Script button. The script gives you programmatic control over the output flowing to the Destination object. The code in the script can look something like this:
' Adjusts the length of output so that columns in the output buffer don't overflow.
Row.InvDateOUT = Mid(Row.InvDate,5,2)
& Mid(Row.InvDate,7,2)
& Left(Row.InvDate,1,4)
If you're not real familiar with DTS programming, get a book on SQL Server Integration Services.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-06-16 : 10:14:52

Yes I am using Export/Import wizard in SSMS.

Will try to look on line for some DTS programming if you have any tips please send them.

Thanks cycledude I will try to attempt what you told me to do this is all new to me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 10:27:03

quote:
Originally posted by JJ297

Yes I am using Export/Import wizard in SSMS.

Will try to look on line for some DTS programming if you have any tips please send them.

Thanks cycledude I will try to attempt what you told me to do this is all new to me.

You can probably refer this. This is a site exclusively for DTS.

http://www.sqldts.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-06-16 : 10:35:40

Thanks headed there now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources