November 18, 2007 at 1:46 pm
: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]
November 18, 2007 at 6:25 pm
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
Change is inevitable... Change for the better is not.
November 18, 2007 at 7:42 pm
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]
November 19, 2007 at 6:30 am
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.
November 19, 2007 at 6:40 am
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:
November 19, 2007 at 7:20 am
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]
November 19, 2007 at 7:46 am
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]
November 19, 2007 at 8:01 am
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.
November 20, 2007 at 12:16 am
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:
November 20, 2007 at 5:58 am
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]
November 20, 2007 at 8:10 am
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.
November 21, 2007 at 1:55 am
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:
November 21, 2007 at 8:02 am
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]
November 21, 2007 at 8:09 am
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]
November 21, 2007 at 12:18 pm
Jazzmeet (11/19/2007)
Tried using the Query that you have given,It return me Date as2007-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.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply