MS Sql Server 2000 DTS and exportation of a DateTimeStamp field.

pts.
Tags:
Development
Can anyone offer assistance in being able to export and transform a 'DateTimeStamp' field using the DTS tool in MS Sql Server 2000. All exportation and transformation to varying destinations do not transfer the complete DateTimeStamp ie. "23/5/2005 22:10:03" transforms to be "23/5/2005 22:10" and all seconds entries are lost. Any ideas on ways to export and transfer a 'DateTimeStamp' exactly as it is represented in sql 2000? I want ot export to a .csv or similar.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Using a SQL query to select the source data — select DateStarted, convert(varchar(16),DateCompleted, 120) as ConvertedDate — both result in the seconds being retained.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Stanton
    Try changing the data type of the output field to char (the default is integer). This worked for me. What I would like to be able to do is get the milleseconds as well and I haven't been able to figure out how to do that.
    0 pointsBadges:
    report
  • Randym
    In the Transformations tab of the Transform Data Task properties, edit the transformation for the date and time column which will bring up the Transformation Options dialog where under the General Tab, you can click Properties to specify the destination format. There, enter dd/MM/yyyy hh:mm:ss. Hope that helps.
    1,740 pointsBadges:
    report
  • HappyGene
    Stuey, MS.s official response to one of our projects was that the seconds values from SQL server would not pass through the other Office products coercion filters. For the two tables where the time stamp was the most reliable single key, I cast it into a string to store it all. There might be a way to cast the dtg as a double before DTS.s export and convert it in-place at the target (for example: with a DATESERIAL style function call but I haven.t pursued it. Mostly because the args for those functions don.t mention seconds. *BUT*, if you print NOW() in Access, or call it from VBA, you get seconds. And, the seconds come through when importing or linking SQL tables (including postIngres, Informix and Oracle.) But that could be an ODBC function. Hmmmmm...sounds like an interesting puzzle. In my experience, unless each target in the DTS spider is more than a gig, or so, Access 2000 and up is the clear winner in controlling the target format. The largest advantage I.ve found with SQL Server 2000 DTS is just the speed due to d-manips being right on the engine. It.s also easier to change transport (ie. mail client or ftp host) in Access. These comments addrss delivery to non-SQL consumers. :) Gene
    0 pointsBadges:
    report
  • Randym
    In the Transform Data Task properties, go to the Transformations tab. Then Edit the transfomation properties of the datetime field (make sure it is a type of DateTime String and not just Copy Column). In the Transform Options dialog, click the Properties button and change the format of the destination to dd/M/yyyy hh:mm:ss. You can add ffff after the ss for milleseconds. Hope this helps.
    1,740 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following