Date format get changed while reading data from SQL

Question

I am reading data from SQL SERVER R2 using edge module in Node. In SQL Server Management Studio, date is in the format 2014-10-05 22:24:00 but when I get it in a JavaScript object, it comes as 05/10/2014 22:24:00 PM. Why does this happen and how can I read date in the same format?

Answer 1

Both of the values are the same, in a data sense; it is only in the formatting that they differ; different programs have different defaults that will be used if a chosen format is not specified. At the point where either of the dates are going to need to be in a specific format, that date will need a formatting command applied to it as a display change, leaving the underlying value untouched. If you just want them to look the same, only one need be changed so that the format of one matches that of the other. You could change the format of the SQL Server date to "DD/MM/YYYY HH:mm:ss" using this script (see this link):

DECLARE @Date DATETIME = '2014-10-05 22:24:00'
SELECT CONVERT(VARCHAR(25), @Date, 103) + ' ' + CONVERT(VARCHAR(25), @Date, 108) AS FormattedDate

This does not have the AM/PM on the end of it - I am not sure why that is appearing in the JavaScript date, give that the hours are given in 24hr format. You could add it if needed by using this:

DECLARE @Date DATETIME = '2014-10-05 22:24:00'
SELECT CONVERT(VARCHAR(26), @Date, 103) + ' ' + CONVERT(VARCHAR(26), @Date, 108) + ' ' + RIGHT(CONVERT(VARCHAR(26),@DATE,109),2) AS FormattedDate

I will admit it is all starting to look pretty clumsy now, though - it might be easier to format both dates to something that is simple to display. There is help in the answers to this question. The simplest solution is to avoid the need for date formatting until the last possible minute, when only your result data need to be played with.

hope that helps.