Rik Smits Rik Smits - 2 months ago 10
Node.js Question

SQL query in NodeJS hours are off when formatting date

I want to format the dtime2 field in my query:

SELECT FORMAT(MAX(dTime),'yyyy-MM-dd hh:mm:ss') FROM triangulations

This gives the output { result: [ { '': '03:34:30' } ], rowcount: 1 }

The hours should be 15. This is also displayed when leaving the format out of the query. Query:
SELECT MAX(dTime) FROM triangulations
gives output:

{ result: [ { '': Mon Jul 17 2017 15:34:30 GMT+0000 (Coordinated Universal Time) } ],
rowcount: 1 }

I execute the query in NodeJs with the library node-mssql-connector.

Why is SQL giving my the wrong hours?

Answer Source

In your format string, yyyy-MM-dd hh:mm:ss, hh means you want the hours in the 12-hour-cycle format, so 3 and 15 are always 3 (AM and PM). Use HH to get them in the 24-hour-cycle format:

yyyy-MM-dd HH:mm:ss

Relevant docs, scroll down to the list of format specifiers.