The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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')

asked 17 Aug '15, 04:17

Christian%20Hamers's gravatar image

Christian Ha...
2365818
accept rate: 0%

edited 19 Aug '15, 05:10

Volker%20Barth's gravatar image

Volker Barth
29.3k287438645


Use uuuuuu for microseconds, iii for milliseconds.

permanent link

answered 17 Aug '15, 09:38

John%20Smirnios's gravatar image

John Smirnios
8.7k377106
accept rate: 40%

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
1

I know, and thanks for the compliment

(19 Aug '15, 06:29) Christian Ha...
showing 2 of 9 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×21
×19
×10
×4

question asked: 17 Aug '15, 04:17

question was seen: 279 times

last updated: 19 Aug '15, 06:29