Solved

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

Posted on 2016-09-07
4
43 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
  • 2
4 Comments
 
LVL 39

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 7

Accepted Solution

by:
Dung Dinh earned 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now