asked on

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

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mike Eghtebas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

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: https://www.experts-exchange.com/articles/12315/SQL-Server-Date-Styles-formats-using-CONVERT.html

fb1990

ASKER

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

Much apppreciated

fb1990

ASKER

Thanks Paul for your help. You have saved me twice this weekend alone. Thank you Sir

PortletPaul

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.