GET milliseconds with DATE_FORMAT in mysql

Question

I want to return a date with a certain format, for the moment I'm using:

SELECT DATE_FORMAT(NOW(3),'%y-%m-%d %H:%i:%s.%f')

which returns:

18-02-26 11:22:07.617000

that's perfect for now, however, I want to get Milleseconds, which means only 3 characters after the seconds, something like: 18-02-26 11:22:07.617 and I'm obliged to do it with DATE_FORMAT.

Do you have any idea?

Answer 1

If you really want use DATE_FORMAT you can do this:

SELECT SUBSTRING(DATE_FORMAT(NOW(3), '%d-%b-%y %H:%m:%s.%f'),1,22);

Answer 2

I was not able to comment on Leo R's answer. There is a typo at the second "%m".

The correct format would be:

SELECT SUBSTRING(DATE_FORMAT(NOW(3), '%d-%b-%y %H:%i:%s.%f'),1,22);