February 26, 2013 at 9:31 pm
Hi
I have a csv file I use as a datasource and I need to pull the data into a table
I'm having trouble converting the date from a string to insert into a date field in the database as the string format is Wednesday 27th March 2013
I cant find any example of making this conversion using a derived column, the closest I could find was in the format Wed 27-03-2013, so you could easily strip off the day name as it was always 3 characters.
Any ideas for how I could convert this into a date format would be greatly appretiated
thanks
February 26, 2013 at 10:15 pm
Is this format consistent: Wednesday 27th March 2013? Always the day name followed by the the day as 1st, 2nd, 3rd, etc., then the full month name, then the full 4 digit year?
February 26, 2013 at 10:21 pm
Hi Lynn
Yes, the formats consistent
February 26, 2013 at 10:41 pm
Not sure how to do this with SSIS, but if you load that date into a staging table and use SQL to convert the string to date, I know how to do that:
declare @DateStr varchar(64) = 'Wednesday 27th March 2013';
select
@DateStr,
substring(@DateStr,
patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr),
len(@DateStr) - patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr) + 1),
left(
substring(@DateStr,
patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr),
len(@DateStr) - patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr) + 1), patindex('% %',
substring(@DateStr,
patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr),
len(@DateStr) - patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr) + 1)) - 3) +
right(
substring(@DateStr,
patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr),
len(@DateStr) - patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr) + 1), len(substring(@DateStr,
patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr),
len(@DateStr) - patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr) + 1)) - patindex('% %',
substring(@DateStr,
patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr),
len(@DateStr) - patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr) + 1)) + 1),
cast(left(
substring(@DateStr,
patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr),
len(@DateStr) - patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr) + 1), patindex('% %',
substring(@DateStr,
patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr),
len(@DateStr) - patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr) + 1)) - 3) +
right(
substring(@DateStr,
patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr),
len(@DateStr) - patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr) + 1), len(substring(@DateStr,
patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr),
len(@DateStr) - patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr) + 1)) - patindex('% %',
substring(@DateStr,
patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr),
len(@DateStr) - patindex('%[ 0-9][0-9][a-z][a-z] %',@DateStr) + 1)) + 1) as DATE);
The above assumes SQL Server 2008 or newer.
February 26, 2013 at 10:44 pm
But then again, hold on a bit, not working with a single digit day. Let me figure that piece out.
February 26, 2013 at 11:11 pm
I am confusing myself now, must be getting tired. I will have to look at this again in the morning after getting some sleep.
February 27, 2013 at 1:00 am
thanks a lot for your effort lynn, that would have taken me days to even get close to that
I'll have a play with it and I'll reply if I can get it working with a single digit day
Thanks!
February 27, 2013 at 7:15 pm
I don't even know how to spell "SSIS". To me, it's a 4 letter word. 😛
Holding you at your word of a consistent format, a bit of the arcane can make this T-SQL code quite short. I don't know about the performance because I haven't tested it for performance, but I suspect it'll be very fast. It'll even tolerate a comma in the expected position.
--===== Conditionally drop the test table to make reruns in SSMS easier.
-- This is NOT a part of the solution.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate the test table on-the-fly.
-- This is NOT a part of the solution.
SELECT StringDate
INTO #TestTable
FROM (
SELECT 'Wednesday 27th March 2013' UNION ALL
SELECT 'Friday 1st February 2013' UNION ALL
SELECT 'Tuesday, 29th February 2000'
) d (StringDate)
;
--===== Convert the string dates to a DATETIME datatype
SELECT tt.StringDate, ca1.LongDate, ca2.ConvertedDateTime
FROM #TestTable tt
CROSS APPLY (SELECT SUBSTRING(tt.StringDate,CHARINDEX(' ' ,tt.StringDate)+1,8000)) ca1 (LongDate)
CROSS APPLY (SELECT DATEADD(DD,
SUBSTRING(ca1.LongDate, 1, PATINDEX('%[a-z]%',ca1.LongDate COLLATE LATIN1_GENERAL_BIN)-1)-1,
SUBSTRING(ca1.LongDate, CHARINDEX(SPACE(1) ,ca1.LongDate COLLATE LATIN1_GENERAL_BIN) ,8000)
)
) ca2 (ConvertedDateTime)
;
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2013 at 4:35 am
Hi
This works for me... whip off the day name, and the ordinal bit of the day of month, then simply convert what's left to smalldatetime. I've avoided generic patindex use because we know all the possible ordinal values, and that every day name ends with 'day'.
select StringDate,
convert(smalldatetime,replace(replace(replace(replace(substring(StringDate, charindex('day ',StringDate,1) + 4, len(StringDate)),'th ', ' '),'st ', ' '),'nd ', ' '),'rd ', ' ')) as DateDate
from YourStagingTable
Cheers
February 28, 2013 at 4:54 am
Borrowing slightly from Jeff, I think I'd do it like this: -
SELECT StringDate, LongDate, ConvertedDateTime
FROM #TestTable
CROSS APPLY (SELECT RTRIM(LTRIM(REPLACE(REPLACE(StringDate COLLATE LATIN1_GENERAL_BIN, ',', ''), dow, '')))
FROM (VALUES ('Monday'), ('Tuesday'), ('Wednesday'), ('Thursday'), ('Friday'), ('Saturday'), ('Sunday')
) a(dow)
WHERE PATINDEX(dow + '%', StringDate COLLATE LATIN1_GENERAL_BIN) = 1
) ca1(LongDate)
CROSS APPLY (SELECT DATEADD(DD,
SUBSTRING(ca1.LongDate, 1, PATINDEX('%[a-z]%',ca1.LongDate COLLATE LATIN1_GENERAL_BIN)-1)-1,
SUBSTRING(ca1.LongDate, CHARINDEX(SPACE(1) ,ca1.LongDate COLLATE LATIN1_GENERAL_BIN) ,8000)
)
) ca2 (ConvertedDateTime);
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply