Formatting numbers
As far as I can tell, there is no elegant
printf equivalent for
SQL Server to create strings with a specific format. The function that comes closest to the functionality of printf seems to be
format.
with n as (
select 1.00 as um union all
select 1.000001 as um union all
select -1 as um union all
select 0.3 as um union all
select 0.007 as um union all
select 123456 as um
)
select
format(n.um, '0' ),
format(n.um, '#' ),
format(n.um, '0.00' ),
format(n.um, '0.000'),
format(n.um, '#.00' ),
format(n.um, '00.00' )
from
n
--
-- 0 # 0.00 0.000 #.00 00.00
-- ------- ------- --------- ----------- --------- ---------
-- 1 1 1.00 1.000 1.00 01.00
-- 1 1 1.00 1.000 1.00 01.00
-- -1 -1 -1.00 -1.000 -1.00 -01.00
-- 0 0.30 0.300 .30 00.30
-- 0 0.01 0.007 .01 00.01
-- 123456 123456 123456.00 123456.000 123456.00 123456.00
The following few format strings are the ones that I find the most useful for dates:
select
substring(format(sysdatetime(), 'hh:mm' ), 1, 5) hour_min,
substring(format(sysdatetime(), 'HH:mm' ), 1, 5) hour24_min,
substring(format(sysdatetime(), 'hh:mm tt' ), 1, 8) hour_min_xM,
substring(format(sysdatetime(), 'yyyy-MM-dd' ), 1, 10) ymd,
substring(format(sysdatetime(), 'd' , 'de-CH'), 1, 10) swiss_date,
substring(format(sysdatetime(), 'r' ), 1, 29) rfc_1123,
substring(format(sysdatetime(), 's' ), 1, 19) sortable, -- ISO 8601
substring(format(sysdatetime(), 'u' ), 1, 19) universal_sortable
--
-- hour_min hour24_min hour_min_xM ymd swiss_date rfc_1123 sortable universal_sortable
-- -------- ---------- ----------- ---------- ---------- ----------------------------- ------------------- -------------------
-- 03:01 15:01 03:01 PM 2019-09-24 24.09.2019 Tue, 24 Sep 2019 15:01:03 GMT 2019-09-24T15:01:03 2019-09-24 15:01:03