Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 String value to Date

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:00
Fri 28 Jun 2013, 14:51
Mon 4 Feb 2013, 16:58
Mon 29 Apr 2013, 09:01
Fri 5 Apr 2013, 01:05
Mon 4 Feb 2013, 17:06

I'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 @string

SELECT 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.
Go to Top of Page

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'

-- SwePeso
SELECT @Sample,
CAST(SUBSTRING(@Sample, 5, CHARINDEX(',', @Sample) - 5) AS DATE);[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

lemondash
Posting Yak Master

159 Posts

Posted - 2013-08-15 : 11:38:04
Wow - thanks guys.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-08-18 : 12:01:19
Provided that default language of the current session/server is English

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -