Link to home
Avatar of fb1990
fb1990

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
Avatar of chaau
chaau
Flag of Australia image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
Avatar of fb1990
fb1990

ASKER

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

Much apppreciated
Avatar of fb1990

ASKER

Thanks Paul for your help.  You have saved me twice this weekend alone.  Thank you Sir
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.