Help Converting iso 8601 date format to Date

Hello Experts,

I need help extracting the date part of ISO 8601 Date format and converting the date to first if month.  The date is in text format in my database,  For example, I have 20150630T235949-04:00 in my database and I need to extract the date part as 'mm/dd/yyyy' and convert the date to first of the month.  In this case I want the date to be 06/30/2015 and then 06/01/2015.

Thanks in advance for your help
LVL 1
fb1990Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
It is not exactly the ISO8601 According to MSDN. If you check the format 126 you'll notice that they want the yyyy-mm-ddThh:mi:ss.mmmZ format (i.e. dashes between the date parts). Luckily for you, you can still convert your date using the 112 format, however you will have to trim your string to 8 characters, like this:
SELECT CONVERT(datetime, SUBSTRING(string_value, 1, 8), 112)

Open in new window

To get the 1st of the month use this classic conversion:
SELECT DATEADD(month, DATEDIFF(month, 0, CONVERT(datetime, SUBSTRING(string_value, 1, 8), 112)), 0)

Open in new window

And finally, if you want to convert the datetime value to a string value in mm/dd/yyyy format use the CONVERT function again:
SELECT CONVERT(varchar(10), CONVERT(datetime, SUBSTRING(string_value, 1, 8), 112), 101)
SELECT CONVERT(varchar(10), DATEADD(month, DATEDIFF(month, 0, CONVERT(datetime, SUBSTRING(string_value, 1, 8), 112)), 0), 101)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperCommented:
See this wonderful short article all about working with dates from Paul Maxwell.
0
PortletPaulfreelancerCommented:
As you data is a string already, to get the first of the month could be outout from:

SELECT SUBSTRING(string_value, 5, 2) + '/01/' + SUBSTRING(string_value, 1, 4)

This will output as MM/01/YYYY
06/01/2015

---
To convert from that string to datetime or date and get first of month

SELECT CONVERT(datetime,  SUBSTRING(string_value, 1, 6) + '01' , 112)
or
SELECT CONVERT(date,  SUBSTRING(string_value, 1, 6) + '01' , 112)

that is: just take the first 6 chars (YYYYMM), and '01', and voila the first of the month.

---
Now to OUTPUT a converted date/datetime in mm/dd/yyyy format

SELECT CONVERT(varchar(10),CONVERT(date,  SUBSTRING(string_value, 1, 6) + '01' , 112),101)

NB: if using MS SQL Server 2012 or later you can use the FORMAT() function instead of convert() for output control.

for more see: http://www.experts-exchange.com/articles/12315/SQL-Server-Date-Styles-formats-using-CONVERT.html
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

fb1990Author Commented:
Thanks Chauu.  Your solution worked wonders for me.  Thanks to eghtebas for the dandy date conversions.

Much apppreciated
0
fb1990Author Commented:
Thanks Paul for your help.  You have saved me twice this weekend alone.  Thank you Sir
0
PortletPaulfreelancerCommented:
By the way, storage of date/time information as strings isn't a great idea

See: DATE and TIME ... don't be scared, and do it right (the first time)

date/time data types (date, smalldatetime, datetime, datetime2, time) are stored internally as numbers, they are smaller in storage terms and faster in execution. You cannot put an invalid date into a date column e.g. "02/30/2015" could get into a string column, it cannot get into a date column.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.