?
Solved

How to extract a "coded date" from a string field?

Posted on 2016-09-07
4
Medium Priority
?
83 Views
Last Modified: 2016-09-11
Experts,

I have a field that looks like this:

CodedDate
2P201608232016082399348

I need a SQL SELECT script that would extract the coded date starting from characters "3-10". This looks like this "20160823".

I need the result to be a date field:

Like this:
"08/23/2016"

Thanks
0
Comment
Question by:MIKE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 41788654
If they are always in that same position/length you can use a SUBSTRING function like:


SELECT CAST( (SUBSTRING(CodedDate, 3, 10) AS DATE) FROM TableNameHere WHERE SomeID = 1234;


OR see below some useful CONVERT date functions in different display formats:

DECLARE @now datetime
SET @now = GETDATE()
select convert(nvarchar(MAX), @now, 0) as output, 0 as style
union select convert(nvarchar(MAX), @now, 1), 1
union select convert(nvarchar(MAX), @now, 2), 2
union select convert(nvarchar(MAX), @now, 3), 3
union select convert(nvarchar(MAX), @now, 4), 4
union select convert(nvarchar(MAX), @now, 5), 5
union select convert(nvarchar(MAX), @now, 6), 6
union select convert(nvarchar(MAX), @now, 7), 7
union select convert(nvarchar(MAX), @now, 8), 8
union select convert(nvarchar(MAX), @now, 9), 9
union select convert(nvarchar(MAX), @now, 10), 10
union select convert(nvarchar(MAX), @now, 11), 11
union select convert(nvarchar(MAX), @now, 12), 12
union select convert(nvarchar(MAX), @now, 13), 13
union select convert(nvarchar(MAX), @now, 14), 14
--15 to 19 not valid
union select convert(nvarchar(MAX), @now, 20), 20
union select convert(nvarchar(MAX), @now, 21), 21
union select convert(nvarchar(MAX), @now, 22), 22
union select convert(nvarchar(MAX), @now, 23), 23
union select convert(nvarchar(MAX), @now, 24), 24
union select convert(nvarchar(MAX), @now, 25), 25
--26 not valid
union select convert(nvarchar(MAX), @now, 100), 100
union select convert(nvarchar(MAX), @now, 101), 101
union select convert(nvarchar(MAX), @now, 102), 102
union select convert(nvarchar(MAX), @now, 103), 103
union select convert(nvarchar(MAX), @now, 104), 104
union select convert(nvarchar(MAX), @now, 105), 105
union select convert(nvarchar(MAX), @now, 106), 106
union select convert(nvarchar(MAX), @now, 107), 107
union select convert(nvarchar(MAX), @now, 108), 108
union select convert(nvarchar(MAX), @now, 109), 109
union select convert(nvarchar(MAX), @now, 110), 110
union select convert(nvarchar(MAX), @now, 111), 111
union select convert(nvarchar(MAX), @now, 112), 112
union select convert(nvarchar(MAX), @now, 113), 113
union select convert(nvarchar(MAX), @now, 114), 114
union select convert(nvarchar(MAX), @now, 120), 120
union select convert(nvarchar(MAX), @now, 121), 121
--122 to 125 not valid
union select convert(nvarchar(MAX), @now, 126), 126
union select convert(nvarchar(MAX), @now, 127), 127
--128, 129 not valid
union select convert(nvarchar(MAX), @now, 130), 130
union select convert(nvarchar(MAX), @now, 131), 131
--132 not valid
order BY style
0
 
LVL 17

Author Comment

by:MIKE
ID: 41788874
The first select code only returns the text "20160823"

I need this : "08/23/2016"
0
 
LVL 8

Accepted Solution

by:
Dung Dinh earned 2000 total points
ID: 41788974
I am assuming the position for date from 3 to 10. Try with this
DECLARE @x varchar(50) ='2P201608232016082399348'

/* Check if length of @x is more than 12 characters and
parsed data is DATE*/
SELECT CASE WHEN LEN(@x) >= 12 AND
							   ISDATE(SUBSTRING(@x,7,2) + '/' -- MM
							   + SUBSTRING(@x,9,2) + '/' -- DD
							   + SUBSTRING(@x,3,4)) = 1 
	   THEN  
				   SUBSTRING(@x,7,2) + '/' -- MM
				   + SUBSTRING(@x,9,2) + '/' -- DD
				   + SUBSTRING(@x,3,4)

	   ELSE NULL END 
	   AS [Date]

Open in new window

In case, you want to convert the value to DATE data type, you can use CONVERT function
0
 
LVL 17

Author Closing Comment

by:MIKE
ID: 41793526
Thanks
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question