August 16, 2013 at 1:16 pm
hi,
Bad situation that I can't get out of, but, I am able to get the data below extracted from a string... as you can see I get many variations of the dates I need...
I thought about RegEd or using the tally table to give me each character and then coalesce it back together as a date...
I'm lost and have searched here and google for hours...
Any ideas please???
thanks,
PS the gaps in the string are what exists...
create table #temp
(
MyValues varchar(50)
)
insert into #temp values ('3/24/12 Saturday
')
insert into #temp values ('02/18/2013
')
insert into #temp values ('fri 10/19/12
')
insert into #temp values ('11/20/12
')
insert into #temp values ('7/21/12
')
August 16, 2013 at 1:44 pm
This can get really complicated. Given that your data is what appears to be user entered information you have more challenges than just removing characters that aren't part of the date. You have validation issues and you most likely have no way to know what dateformat the information was entered. mdy and dmy can look the same but mean vastly different things. There are dozens of threads around ssc that attempt to deal with this type of thing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 16, 2013 at 2:03 pm
Here is an attempt. I would write a procedural function to add tons of checking before moving this to production.
declare @temp table (MyValues varchar(50));
insert into @temp values ('3/24/12 Saturday
')
insert into @temp values ('02/18/2013
')
insert into @temp values ('fri 10/19/12
')
insert into @temp values ('11/20/12
')
insert into @temp values ('7/21/12
')
select
MyValues,
SUBSTRING(MyValues, PATINDEX('%[0-9]%',MyValues), LEN(MyValues) - PATINDEX('%[0-9]%',MyValues) - PATINDEX('%[0-9]%',reverse(MyValues)) + 2)
as string,
case when isdate(SUBSTRING(MyValues, PATINDEX('%[0-9]%',MyValues), LEN(MyValues) - PATINDEX('%[0-9]%',MyValues) - PATINDEX('%[0-9]%',reverse(MyValues)) + 2))=1
then cast(SUBSTRING(MyValues, PATINDEX('%[0-9]%',MyValues), LEN(MyValues) - PATINDEX('%[0-9]%',MyValues) - PATINDEX('%[0-9]%',reverse(MyValues)) + 2) as date)
else null
end
as ExtractedDate
from
@temp
;
August 16, 2013 at 4:15 pm
What you seek to do is literally impossible without some identification of the type of date format provided. The reason is that you can have a at least 2 valid dates of differing days with the same collection of characters depending on the date format used. And you can also obviously have invalid date and valid date combinations too, but cannot be certain which is correct.
There are ways to iterate the various formats and flag if they are valid dates or not and do a "best guess" at deriving something valid and then picking one of the valid dates if multiple pop out.
Best of luck - I don't envy you the task.:hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 19, 2013 at 7:24 am
Thanks for the code Bill... Works great when there's a date, but think it broke when it runs into text or some other format that i didn't provide as an example...
Kevin, you are 100% right!!! This helps prove to the users that want me automate so much, that using notes to report off of is ridiculous and that extra step proves worthwhile, and add only a second or two to your day to do it right...
thanks,
john
August 19, 2013 at 8:59 am
As you're using SQL Server 2012, why won't you use TRY_CONVERT with a string Splitter[/url]?
WITH CTE AS(
SELECT TRY_CONVERT( DATETIME, Item) AS MyDate
FROM #temp t
CROSS APPLY dbo.DelimitedSplit8K( MyValues, ' ')
)
SELECT MyDate
FROM CTE
WHERE MyDate IS NOT NULL
August 19, 2013 at 12:06 pm
Luis Cazares (8/19/2013)
As you're using SQL Server 2012, why won't you use TRY_CONVERT with a string Splitter[/url]?
WITH CTE AS(
SELECT TRY_CONVERT( DATETIME, Item) AS MyDate
FROM #temp t
CROSS APPLY dbo.DelimitedSplit8K( MyValues, ' ')
)
SELECT MyDate
FROM CTE
WHERE MyDate IS NOT NULL
Luis - That is really cool. I don't have 2012 to play with, so I've not gotten to play with TRY_CONVERT. I don't think it'll solve the problem of MDY and DMY being misinterpreted and I think the OP is essentially stuck because of this single issue, but your approach to it is truly slick. My sincerest compliments - I shall file that one away for if/when we do get 2012!
August 19, 2013 at 12:33 pm
Ed Wagner (8/19/2013)
Luis Cazares (8/19/2013)
As you're using SQL Server 2012, why won't you use TRY_CONVERT with a string Splitter[/url]?
WITH CTE AS(
SELECT TRY_CONVERT( DATETIME, Item) AS MyDate
FROM #temp t
CROSS APPLY dbo.DelimitedSplit8K( MyValues, ' ')
)
SELECT MyDate
FROM CTE
WHERE MyDate IS NOT NULL
Luis - That is really cool. I don't have 2012 to play with, so I've not gotten to play with TRY_CONVERT. I don't think it'll solve the problem of MDY and DMY being misinterpreted and I think the OP is essentially stuck because of this single issue, but your approach to it is truly slick. My sincerest compliments - I shall file that one away for if/when we do get 2012!
Maybe with the third argument of TRY_CONVERT the problem can be solved if the users follow the same format. It's up to John to test with.
August 19, 2013 at 1:02 pm
I will for sure be trying this...
I have to fix code that extract's the date from the text; so I can try my example data I posted and see how that works, which sounds like it will work great...
Is that Function like an IsDate() but on Steriods?
Thanks again,
John
August 19, 2013 at 1:07 pm
jsteinbeck-618119 (8/19/2013)
I will for sure be trying this...I have to fix code that extract's the date from the text; so I can try my example data I posted and see how that works, which sounds like it will work great...
Is that Function like an IsDate() but on Steriods?
Thanks again,
John
Try_Convert is the same that Convert but instead of throwing errors for invalid transformations it returns nulls. Using the code I posted, you should be able to obtain all valid dates without an additional date extraction from the text.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply