Monday, May 8, 2017

Getting to ISO format 'yyyyMMdd' with a single function?

The Goal:


Convert a date in SQL Server into the ISO format 'yyyyMMdd' with a single function, and without having to CAST a character value into an integer.  Alas, we have yet to find such an approach.

Before the FORMAT command, the most expedient approach was to use the CONVERT function, combined with a second convert function, or CAST the value to an integer value.  For example:


 declare @adate date = '2017-05-01';
 select 
 ISOdate_CONVERT = cast(convert(varchar(12), @adate,112) as int);


FORMAT


Starting with SQL Server 2012, we were introduced to the FORMAT statement. It had promise, but like the CONVERT statement, we still had to issue a second CAST statement. Output from FORMAT is nvarchar. Sure, we could skip the second CAST function, and just rely on the implicit conversion from nvarchar to integer, but that would be poor form.  SQL Server still has to make the conversion, and we have to believe that an implicit conversion is not only sloppy, but perhaps a bit slower. So, with FORMAT, we now have two options to convert a date, or datetime into an ISO formatted date - yyyyMMdd.

 declare @adate date = '2017-05-01';
 select 
 ISOdate_CONVERT = cast(convert(varchar(12), @adate,112) as int)
,ISOdate_FORMAT  = cast(format(@adate,'yyyyMMdd','en-US')as int); 


Which to use?

Since have not done any performance testing, either one would suffice. What we would all like is a method of converting a date directly into an ISO format, with a single function.

No comments: