Author |
Topic |
lemondash
Posting Yak Master
159 Posts |
Posted - 2013-08-15 : 09:51:04
|
Hello, I'm currently working on a project, i have a source of data that exports the date as a sting.This data needs to go to a date field.Here are some examples.Mon 4 Feb 2013, 17:00Fri 28 Jun 2013, 14:51Mon 4 Feb 2013, 16:58Mon 29 Apr 2013, 09:01Fri 5 Apr 2013, 01:05Mon 4 Feb 2013, 17:06I've wrote this and it is a work in progress.Does anybody have any ideas if there is a better was of doing this ?DECLARE @string VARCHAR(30)SET @string = 'Fri 28 Jun 2013, 14:51'SELECT @stringSELECT CONVERT(DATE, SUBSTRING(@string, 5, 2) + '-' + CASE WHEN SUBSTRING(@string, 8, 3) = 'Jan' THEN '01' WHEN SUBSTRING(@string, 8, 3) = 'Feb' THEN '02' WHEN SUBSTRING(@string, 8, 3) = 'Mar' THEN '03' WHEN SUBSTRING(@string, 8, 3) = 'Apr' THEN '04' WHEN SUBSTRING(@string, 8, 3) = 'May' THEN '05' WHEN SUBSTRING(@string, 8, 3) = 'Jun' THEN '06' WHEN SUBSTRING(@string, 8, 3) = 'Jul' THEN '07' WHEN SUBSTRING(@string, 8, 3) = 'Aug' THEN '08' WHEN SUBSTRING(@string, 8, 3) = 'Sep' THEN '09' WHEN SUBSTRING(@string, 8, 3) = 'Oct' THEN '10' WHEN SUBSTRING(@string, 8, 3) = 'Nov' THEN '11' WHEN SUBSTRING(@string, 8, 3) = 'Dec' THEN '12' ELSE 'No Value Found' END + '-' + SUBSTRING(@string, 12, 4)) AS [Date]Any help would be great |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-15 : 10:08:04
|
My first suggestion would be to see if you can twist arms to get the data in a more decent form - for example as standard ISO formatted data. If that is not possible, here is one possible approach, but it can fail unless the format is very consistent.DECLARE @string VARCHAR(30)SET @string = 'Fri 28 Jun 2013, 14:51'SELECT LEFT(x, CHARINDEX(',',x+',')-1)FROM( SELECT STUFF(@string,1,CHARINDEX(' ' ,@string+' '),'') x) s Editing: And, forgot to mention - it works only with the correct language setting. So if you have a need for internationalization/localization, this won't work. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-15 : 10:09:06
|
[code]DECLARE @Sample VARCHAR(30) = 'Fri 10 Jun 2013, 14:51'-- SwePesoSELECT @Sample, CAST(SUBSTRING(@Sample, 5, CHARINDEX(',', @Sample) - 5) AS DATE);[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
lemondash
Posting Yak Master
159 Posts |
Posted - 2013-08-15 : 11:38:04
|
Wow - thanks guys. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-08-18 : 12:01:19
|
Provided that default language of the current session/server is EnglishMadhivananFailing to plan is Planning to fail |
|
|
|
|
|