Solved

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

Posted on 2016-09-07
4
58 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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 and will be exposed to the many uses the SELECT statement has.

785 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