Date Transformation from YYYYMMDD

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: bradm98 (bradm98_at_discussions.microsoft.com)
Date: 01/07/05 

Date: Fri, 7 Jan 2005 10:05:05 -0800

I'm very new to DTS, but here goes:
I've got a source file with dates stored as YYYYMMDD, and I'd like to move
the data into a datetime field. I've figured out the basic transformation
steps, but I'm running into a problem with source dates = '00000000' (e.g.
obsolete_date = '00000000' for products that are not obsolete).

>From a 'Best Practices' standpoint, what is the best way to handle this?
I've considered a few options, but I'm not experienced enough to anticipate
the cost/benefit of each down the road:

1. Change destination field def to match source (char(8)) - I'll be using a
fair amount of date functions, though, so this adds extra work for all
queries, etc.
2. Script a transformation that changes '00000000' to NULL
2. Script a transformation that changes '00000000' to some other value
(e.g. Dec 31, 9999)

Any other options? What else should I consider when deciding?

Relevant Pages

  • Re: Writing to task log file
    ... You cannot use the log file from within a transformation. ... It is not available in transformation or workflow scripts. ... >> One option for logging inside a transformation script is to use a lookup ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS Transformation Error
    ... script that the transformation should also copy the Source Table structure ... ' Visual Basic Transformation Script ... ' Copy each source column to the destination column ... programming though I'm reasonably familiar with the DTS UI. ...
    (microsoft.public.sqlserver.dts)
  • Re: SSIS & Script Transformation (debug vs normal)
    ... Can you give more details about what the "transformation script" is ... The package is pretty simple. ... but not when ran by sql agent. ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS - aus Space wird Null
    ... Ich habe das Package mit dem "DTS Import/Export Wizard" ... Script generiert wird. ... Welche genau machen könnte, dass da die Transformation Leere Felder in NULL Felder umwandelt, weiss ich nicht. ...
    (microsoft.public.de.sqlserver)
  • Re: Package executes on one machine but not another
    ... I've tracked the error message down to the following. ... As part of the transformation that's failing I assign the Global ... Dictionary object to a local variable i.e. one just dim'd in the ... transformation script. ...
    (microsoft.public.sqlserver.dts)