on 2015 Aug 17 5:17 AM
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:
SELECT Replace(DateFormat(CURRENT TIMESTAMP, 'yyyymmddhhnnss.sss'), '.', '')
I would like to be able to do:
SELECT DateFormat(CURRENT TIMESTAMP, 'yyyymmddhhnnsszzz')
Request clarification before answering.
Use uuuuuu for microseconds, iii for milliseconds.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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*
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".
> "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 🙂
I know, and thanks for the compliment
User | Count |
---|---|
87 | |
10 | |
9 | |
8 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.