SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Lookup_BI
Lookup_BI
SSChasing Mays
SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)

Group: General Forum Members
Points: 648 Visits: 642
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.

“I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)

Group: General Forum Members
Points: 205969 Visits: 41952
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lookup_BI
Lookup_BI
SSChasing Mays
SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)

Group: General Forum Members
Points: 648 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
Dave23
Dave23
SSC Eights!
SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)

Group: General Forum Members
Points: 917 Visits: 1806
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.
ARJUNA DAS
ARJUNA DAS
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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)Cool
Lookup_BI
Lookup_BI
SSChasing Mays
SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)

Group: General Forum Members
Points: 648 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
Lookup_BI
Lookup_BI
SSChasing Mays
SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)

Group: General Forum Members
Points: 648 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
Dave23
Dave23
SSC Eights!
SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)

Group: General Forum Members
Points: 917 Visits: 1806
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.
ARJUNA DAS
ARJUNA DAS
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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)Cool
Lookup_BI
Lookup_BI
SSChasing Mays
SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)

Group: General Forum Members
Points: 648 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search