AFAIK If you only want the fractions of a second there is no direct way you can not get them without a preceding ".". It would be nice if a symbol was available to just get the fractions. So instead of:
I would like to be able to do:
|
Use uuuuuu for microseconds, iii for milliseconds. 1
Wow, that's a quick solution: SELECT DateFormat(CURRENT TIMESTAMP, 'yyyymmddhhnnss.ssssss') as "Default", DateFormat(CURRENT TIMESTAMP, 'yyyymmddhhnnssiii') as w_milliseconds, DateFormat(CURRENT TIMESTAMP, 'yyyymmddhhnnssuuuuuu') as w_microseconds Default w_milliseconds w_microseconds 20150817155831.228000 20150817155831228 20150817155831228000
(17 Aug '15, 09:59)
Volker Barth
Replies hidden
1
In case anyone's wondering why it's not documented :)... BEGIN DECLARE d TIMESTAMP = CAST ( '2015-01-01 12:12:12.345678' AS TIMESTAMP ); SELECT 1 AS "#", DATEFORMAT ( d, 'yyyymmddhhnnss.ssssss' ) AS s UNION ALL SELECT 2, DATEFORMAT ( d, 'yyyymmddhhnnss.sssss' ) UNION ALL SELECT 3, DATEFORMAT ( d, 'yyyymmddhhnnss.ssss' ) UNION ALL SELECT 4, DATEFORMAT ( d, 'yyyymmddhhnnss.sss' ) UNION ALL SELECT 5, DATEFORMAT ( d, 'yyyymmddhhnnss.ss' ) UNION ALL SELECT 6, DATEFORMAT ( d, 'yyyymmddhhnnss.s' ) UNION ALL SELECT 11, DATEFORMAT ( d, 'yyyymmddhhnnssiiiiii' ) UNION ALL SELECT 12, DATEFORMAT ( d, 'yyyymmddhhnnssiiiii' ) UNION ALL SELECT 13, DATEFORMAT ( d, 'yyyymmddhhnnssiiii' ) UNION ALL SELECT 14, DATEFORMAT ( d, 'yyyymmddhhnnssiii' ) UNION ALL SELECT 15, DATEFORMAT ( d, 'yyyymmddhhnnssii' ) UNION ALL SELECT 16, DATEFORMAT ( d, 'yyyymmddhhnnssi' ) UNION ALL SELECT 21, DATEFORMAT ( d, 'yyyymmddhhnnssuuuuuu' ) UNION ALL SELECT 22, DATEFORMAT ( d, 'yyyymmddhhnnssuuuuu' ) UNION ALL SELECT 23, DATEFORMAT ( d, 'yyyymmddhhnnssuuuu' ) UNION ALL SELECT 24, DATEFORMAT ( d, 'yyyymmddhhnnssuuu' ) UNION ALL SELECT 25, DATEFORMAT ( d, 'yyyymmddhhnnssuu' ) UNION ALL SELECT 26, DATEFORMAT ( d, 'yyyymmddhhnnssu' ) ORDER BY 1; END; # s 1 20150101121212.345678 2 20150101121212.34567 3 20150101121212.3456 4 20150101121212.345 5 20150101121212.34 6 20150101121212.3 11 20150101121212000345 12 2015010112121200345 13 201501011212120345 14 20150101121212345 15 20150101121212** 16 20150101121212* 21 20150101121212345678 22 20150101121212***** 23 20150101121212**** 24 20150101121212*** 25 20150101121212** 26 20150101121212*
(17 Aug '15, 13:56)
Breck Carter
So you say it doesn't follow that Watcom Rule? :)
(17 Aug '15, 15:27)
Volker Barth
I don't know what I'm saying... maybe that REPLACE ( DATEFORMAT ( d, 'yyyymmddhhnnss.ssssss' ), '.', '' ) is documented :)
(17 Aug '15, 20:06)
Breck Carter
I think "iii" and "uuuuuu" behave similar to "hh" and "nn" when used with an unfitting number of repetitions - they all add leading zeroes when used with too many characters, and return asteriks when used with too little: BEGIN DECLARE d TIMESTAMP = CAST ( '2015-01-01 12:15:16.345678' AS TIMESTAMP ); SELECT 1 AS "#", DATEFORMAT ( d, 'yyyymmddhhnnnnnn' ) AS s UNION ALL SELECT 2, DATEFORMAT ( d, 'yyyymmddhhnnnnn' ) UNION ALL SELECT 3, DATEFORMAT ( d, 'yyyymmddhhnnnn' ) UNION ALL SELECT 4, DATEFORMAT ( d, 'yyyymmddhhnnn' ) UNION ALL SELECT 5, DATEFORMAT ( d, 'yyyymmddhhnn' ) UNION ALL SELECT 6, DATEFORMAT ( d, 'yyyymmddhhn' ) UNION ALL SELECT 11, DATEFORMAT ( d, 'yyyymmddhhhhhh' ) UNION ALL SELECT 12, DATEFORMAT ( d, 'yyyymmddhhhhh' ) UNION ALL SELECT 13, DATEFORMAT ( d, 'yyyymmddhhhh' ) UNION ALL SELECT 14, DATEFORMAT ( d, 'yyyymmddhhh' ) UNION ALL SELECT 15, DATEFORMAT ( d, 'yyyymmddhh' ) UNION ALL SELECT 16, DATEFORMAT ( d, 'yyyymmddh' ) ORDER BY 1; END; 1 2015010112000015 2 201501011200015 3 20150101120015 4 2015010112015 5 201501011215 6 2015010112* 11 20150101000012 12 2015010100012 13 201501010012 14 20150101012 15 2015010112 16 20150101* I guess the Watcom Rule expects you to use the format specifier reasonable here:) What is irritating me* is the fact that date parts are not rounded, I would think "345678 µs" should be returned as "346 ms", not "345 ms".
(18 Aug '15, 01:44)
Volker Barth
1
> "iii" and "uuuuuu" behave similar to "hh" and "nn" ...and NOT like "sssssss" which is a fraction unlike all the other components. Good catch! This should all be documented, but I suspect it's not as sexy as Fiori :)
(18 Aug '15, 04:58)
Breck Carter
I don't think it needs documentation, cf. the new tag:)
(19 Aug '15, 05:11)
Volker Barth
...as long as Christian knows the new tag is NOT intended as mockery of his excellent question (three votes no less)
(19 Aug '15, 05:32)
Breck Carter
|