February 10, 2012 at 12:24 pm
Thanks in advance for any guidance on this topic. As you can imagine, I am still at newb status and I have run across an issue I can't BOL- or Google-search my way out of 🙂
I have a list of invoice records from a proprietary system (not owned by my company) in the following (csv) format:
"UID","Full Name","Date","ECC#","Type","Description","Amount","Proc. Fee","Difference"
"aa000","Name N Name","Mon Jan 4 15:50:49 2010","00000000","Credit Card","Annual Dues","-999.00","-1.00","-998.00"
"bb000","Name2 Name2","Tue Jan 5 12:40:45 2010","00000001","Credit Card","Annual Dues","-999.00","-1.00","-998.00"
The Date column is giving me fits because of the formatting. Nearest I can figure out is that it is an Oracle or db2 style timestamp (non-scientific guess). I believe the components are: Day of week, Month, day, timestamp(hh:mm:ss format), year. I have NO idea how to parse this. I took a stab at a Derived column using an expression something like:
REPLACE([Date], SUBSTRING([Date], RIGHT([Date], 13), 8), "")
But I get the following error:
The function "SUBSTRING" does not support the data type "DT_WSTR" for parameter number 2...(there's more which can be included if needed)
Obviously, my Expression Fu skills are weak! I have verified that the column is set as DT_STR but obviously I am missing something (probably many things) here. Also note that there are some double spaces in the Date and Name columns. Any help would be appreciated.
February 10, 2012 at 2:08 pm
Your error sounds like an SSIS error, not a TSQL error.
This is a TSQL solution:
select
TrimmedString= right(DT,19),
[Date]= convert(datetime,right(DT,19))
from
( -- Test Data
select DT ='Mon Jan 4 15:50:49 2010'union all
select DT ='Tue Jan 5 12:40:45 2010'
) a
Results:
TrimmedString Date
------------------- -----------------------
Jan 4 15:50:49 2010 2010-01-04 15:50:49.000
Jan 5 12:40:45 2010 2010-01-05 12:40:45.000
(2 row(s) affected)
February 10, 2012 at 2:21 pm
suggest this to work with two digit dates
[Date]= convert(datetime,right(DT,20))
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 10, 2012 at 3:14 pm
Michael Valentine Jones (2/10/2012)
Your error sounds like an SSIS error, not a TSQL error.
Yes sir, you are correct. I should have specified that in the OP.
Results:
TrimmedString Date------------------- -----------------------
Jan 4 15:50:49 2010 2010-01-04 15:50:49.000
Jan 5 12:40:45 2010 2010-01-05 12:40:45.000
(2 row(s) affected)
Let me se if I get what you are saying: specifically, T-SQL will recognize the unorthodox format as long as I convert(datetime...)?
So, do you have a suggestion as to which I should use if I am going to be making updates frequently (minimum a couple of times a week but more likely at least once a day)?
Also, I will use the right(DT,20) clause because having both numbers for a month sounds like a good idea 🙂
Again, thanks for the assistance!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply