Why I Love The ISO Date Format

Wednesday, November 13th 2013

After working with different types of data for many years you gain appreciation for a few things. One of the odd things I've gain favor of is the ISO Date Format (YYYYMMDD). This simple concatenation of a four digit year (YYYY), 2 digit month (MM) and 2 digit day (DD) makes it very easy to query and work with. Not to mention this format is internationally recognized and compatible with gregorian and julian calendars. With or without built in programing/scripting language functions I can break this date format down into easy to use parts. Let me show you a few PHP snippets I've used in the past.

Example: Parsing ISO Date Format Using date() and strtotime() Functions $str_date = 20131231;
//strtotime() will convert it to Unix Time, date() returns part requested
$year = date("Y",strtotime($str_date)); //2013 - "Y"
$month = date("m"),strtotime($str_date)); //12 - "m"
$day = date("d"),strtotime($str_date)); //31 - "d"
echo $year.$month.$day; //20131231
Example: Parsing ISO Date Format Using substr() Function $str_date = 20131231;
//even without the use of date() and strtotime() functions, I can parse this date format into year, month, day using the substr() function
$year = substr($str_date,0,4); //2013
$month = substr($str_date,4,2); //12
$day = substr($str_date,6,2); //31
echo $year.$month.$day; //20131231

Where I think this date format really shines is in SQL queries. Because of the nature of the date format being an integer it makes it easy to find all the dates between a range of dates. For example say if I wanted all records where the date is between January 1st 2013 and October 21st 2013. My query would look something like the example below.

SELECT * FROM records WHERE mydate BETWEEN 20130101 AND 20131021;

What if I needed a quick and dirty way of querying for all records during one month (April 2013) or even a full year (2013). My queries could look something like the examples below.

SELECT * FROM records WHERE mydate LIKE '201304%'; #Full Year-Month
SELECT * FROM records WHERE mydate LIKE '2013%'; #Full Year

One last thing about this date format. How can we convert to it? Well Below are some examples in different languages. I hope this may have inspired you to take a look at this date format. I know it makes my life easier as a developer at times. In a future article I'll talk about the benefits of using Unix timestamps as well.

#php (UNIX TIMESTAMP to ISO): date("Ymd"),strtotime("yourdatehere"));
#mysql (MYSQL Datetime to ISO): date_format(date(NOW(),'%Y%m%d'))
#javascript (JS Date to ISO): new Date().toISOString().slice(0,10).replace(/-/g,"")

#oracle (ISO to Oracle Date): to_date('20170101','yyyymmdd')
#mssql (ISO to MSSQL Date): CAST(CAST('20170101' AS varchar) AS datetime)

 Sources: w3.org
  0

Categories: Snippets

Tags: development, mysql, php