SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 String value to Date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lemondash
Posting Yak Master

United Kingdom
159 Posts

Posted - 08/15/2013 :  09:51:04  Show Profile  Click to see lemondash's MSN Messenger address  Reply with Quote
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
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 08/15/2013 :  10:08:04  Show Profile  Reply with Quote
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.

Edited by - James K on 08/15/2013 10:08:45
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/15/2013 :  10:09:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE @Sample VARCHAR(30) = 'Fri 10 Jun 2013, 14:51'

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



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

lemondash
Posting Yak Master

United Kingdom
159 Posts

Posted - 08/15/2013 :  11:38:04  Show Profile  Click to see lemondash's MSN Messenger address  Reply with Quote
Wow - thanks guys.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 08/18/2013 :  12:01:19  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Provided that default language of the current session/server is English

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000