Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

**************DD/MM/YY to MM/DD/YY************ Expand / Collapse
Author
Message
Posted Sunday, November 18, 2007 1:46 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 31, 2009 7:53 AM
Points: 150, Visits: 642

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.



“I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison
Post #423392
Posted Sunday, November 18, 2007 6:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #423410
Posted Sunday, November 18, 2007 7:42 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 31, 2009 7:53 AM
Points: 150, Visits: 642
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 ?


“I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison
Post #423413
Posted Monday, November 19, 2007 6:30 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 18, 2014 2:05 PM
Points: 252, Visits: 1,720
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.
Post #423555
Posted Monday, November 19, 2007 6:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 15, 2008 7:36 AM
Points: 17, Visits: 86
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)
Post #423560
Posted Monday, November 19, 2007 7:20 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 31, 2009 7:53 AM
Points: 150, Visits: 642
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


“I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison
Post #423581
Posted Monday, November 19, 2007 7:46 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 31, 2009 7:53 AM
Points: 150, Visits: 642
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 ?




“I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison
Post #423609
Posted Monday, November 19, 2007 8:01 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 18, 2014 2:05 PM
Points: 252, Visits: 1,720
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.
Post #423618
Posted Tuesday, November 20, 2007 12:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 15, 2008 7:36 AM
Points: 17, Visits: 86
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)
Post #423908
Posted Tuesday, November 20, 2007 5:58 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 31, 2009 7:53 AM
Points: 150, Visits: 642
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?


“I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison
Post #424028
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse