**************DD/MM/YY to MM/DD/YY************

  • :crying:

    Hi,

    I need help on the following.I have a column 'Date' in Staging table with the following

    Data type=varchar(50)

    Format/Style='dd/mm/yy'

    Example='18/11/07'

    I need to convert 'Date' column to the following .This conversion would take place from a staging table to a destination table.

    Data type = datetime

    Format/Style ='mm/dd/yy'

    Example='11/18/07'

    I have tried the following query

    SELECT Date,

    CONVERT(varchar(50),Date,101)

    FROM staging_table

    BUT NOTHING CHANGED .Where am I going wrong ?

    I need to include this conversion in my SSIS package.

    [font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]

  • Converting a VARCHAR date to a VARCHAR date get's you nothing... you must first convert to DATETIME.

    Also, storing dates as VARCHAR in a table to start with is a form of "Death by SQL".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi,

    I would be really grateful, if you could guide me through the steps that I need to take.What do I use CONVERT @ CAST ?

    [font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]

  • To go from a varchar date: 'dd/mm/yy' to a mm/dd/yy datetime in SSIS, you could use something like the following as the expression in a derived column transformation:

    ISNULL(CHAR_DATE) ? NULL(DT_DATE) : (dt_date)(SUBSTRING(CHAR_DATE,4,2) + "/" + SUBSTRING(CHAR_DATE,1,2) + "/" + SUBSTRING(CHAR_DATE,7,2))

    If you didn't have the freedom to modify the data type from varchar to datetime, you could still do something similar, but I absolutely agree with Jeff, try to avoid storing dates as varchars if you have the option.

  • Hi ,

    Not sure that the following code will help u or not. If it then u may put it into proc or can use it to update.Given below example as a '29/10/2007' (assume as a variable in this format)

    select

    convert(datetime,

    substring('29/10/2007',charindex('/','29/10/2007')+1,2)+'/'+

    left('29/10/2007',charindex('/','29/10/2007')-1)+'/'+

    right('29/10/2007',charindex('/',reverse('29/10/2007'))-1)

    )

    thnx,

    Arjuna

    Orange (India)

    Thnx/Regds,
    Mangu
    Orange Business Services(India):cool:

  • Tried using the Query that you have given,It return me Date as

    2007-10-29 00:00:00.000 .Which is in YYYY-MM-DD HH:MM:SS format.I need the Date in MM/DD/YY.

    Hope someone could help me out on this

    [font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]

  • Hi Smith,

    I added a derived column transformation and added the expression as per your advise as below:-

    "ISNULL(Date) ? NULL(DT_DATE) : (DT_DATE)(SUBSTRING(Date,4,2) + "/" + SUBSTRING(Date,1,2) + "/" + SUBSTRING(Date,7,2))"

    I get an error output as

    [Derived Column [610]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (610)" failed because error code 0xC0049063 occurred, and the error row disposition on "input column "Date" (658)" 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.

    In Source Table [Staging ], Date column has a varchar(50) data type

    In Destination Table, Date column has datetime data type.

    Is there any other setting that I am missing ?

    [font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]

  • The expression looks right to me, is there any possibility that the incoming field is not formatted correctly in the source data? The error message seems to indicate a conversion failure, so the string components aren't being reassembled into a valid date. You might consider setting up an exception table, and setting up an error trap to see what the value is. Then you can set the error condition to redirect row instead of fail component. I process a lot of very dirty source data, so all of my packages use a setup like this.

    You might also consider throwing a trim call on the inbound varchar prior to substringing it. It can affect performance, but I've had to do it on a few occasions.

  • Hi,

    check it :

    select

    convert(varchar,convert(datetime,

    substring('29/10/2007',charindex('/','29/10/2007')+1,2)+'/'+

    left('29/10/2007',charindex('/','29/10/2007')-1)+'/'+

    right('29/10/2007',charindex('/',reverse('29/10/2007'))-1)

    ),101)

    Output: 10/29/2007

    Thnx/Regds,
    Mangu
    Orange Business Services(India):cool:

  • It is the source data. There were some datas with dd/mm/yy and some datas were dd/mm/yyyy format. the one with yyyy comes okay after conversion but the one with only yy returns a weird set of result. Example: 18/11/07 becomes 2018/11/07

    1. How do I repair the bad format that I am already receiving from the source? Must fix ALL to be yyyy in stead of yy.How can I do this?

    2. I want it to become american standard, which it should be 11/18/07 and not British standard which is what I am getting now.

    3. What is the code to do ALL these in a single query so that in SSIS I would save the performance?

    [font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]

  • If you re-order the date string into the format 'mm-dd-yy' and then run a year( ) function call on it, it will convert the two-digit year into a four-digit year. You can then build the output string using the months, days, and that four-digit year prior to casting it to a dt_date.

  • Date data 101;

    Date data should always be stored as datetime type in the database (YYYYMMDDHHmmss) and then converted to the correct format at the user interface/reporting function for user interaction/display purposes only.

    Correct me if I am wrong but that makes things ISO compliant and delocalized.

    --Shaun

    Hiding under a desk from SSIS Implemenation Work :crazy:

  • Storing date as a string is a sick idea but if you have to have it your way this is it:

    SELECT CONVERT(VARCHAR, CONVERT(DATETIME, '18/11/07', 3), 1)

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • The above works with DD/MM/YY only. For dates in DD/MM/YYYY format use 103 instead of 3.

    You can use the CASE to check the length of the input and use both of them in the same statement.

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • Jazzmeet (11/19/2007)


    Tried using the Query that you have given,It return me Date as

    2007-10-29 00:00:00.000 .Which is in YYYY-MM-DD HH:MM:SS format.I need the Date in MM/DD/YY.

    Hope someone could help me out on this

    It returns it that way because you said you needed to store it as a datatype of Datetime.

    This is the preferrable way to store this data btw. When it is displayed by your client application the application should be coded so that it displays the data based on the local machine's regional setting.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply