CODEWITHSUNDEEP
sql-servert-sqldatetime
Simon Jensen
Simon Jensen

Reputation: 309

Why is my SQL datetime being stored in the wrong format?

I'm trying to save and retrieve a date and time in and from a SQL 2014 Express database via a form on a classic ASP site.

The main idea behind this is that no matter what setup this script will be running under, the time format will always be the same.

I store the date and time as datetime2 in SQL in the format dd-mm-yyyy hh:nn:ss, yet it stores it as yyyy-dd-mm hh:nn:ss. As I retrieve the date it writes it as dd/mm/yyyy hh:nn:ss AM/PM! Why?

The computer has been setup with correct time and date format, and the SQL database has been set to inherit the Windows time and date format.

This code constructs the time format that I want (dd-mm-yyyy hh:nn:ss):

<%
Dim dd, mm, yy, hh, nn, ss
Dim datevalue, timevalue, dtsnow, dtsvalue

dtsnow = Now()


dd = Right("00" & Day(dtsnow), 2)
mm = Right("00" & Month(dtsnow), 2)
yy = Year(dtsnow)
hh = Right("00" & Hour(dtsnow), 2)
nn = Right("00" & Minute(dtsnow), 2)
ss = Right("00" & Second(dtsnow), 2)


datevalue = dd & "-" & mm & "-" & yy

timevalue = hh & ":" & nn & ":" & ss

dtsvalue = datevalue & " " & timevalue
%>

I try to get the date and time and sort it descending. This part works, though it still comes in the wrong date format and AM/PM, and therefore the sorting order will look randomized and not come in order.

strSQL = "SELECT * FROM dbo.notes WHERE tag = '" & tagname & "' ORDER BY dt DESC"

I therefore try to apply these conversions to make the date and time come out just right: dd-mm-yyyy hh:nn:ss

strSQL = "SELECT FORMAT(dt, 'dd-MM-yyyy HH:mm:ss', 'de-de') as tag,username,dt,note FROM dbo.notes WHERE tag = '" & tagname & "' ORDER BY dt DESC"

Yet this will not work as it then comes up with an "An error occurred on the server when processing the URL" OR it writes both date and time formats yet only one date and time have been stored in the database.

How can I solve this?

Upvotes: 1

Views: 5798

Answers (1)

Bartosz X
Bartosz X

Reputation: 2798

FORMAT() is the SQL Server 2012+ function you are looking for:

SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd hh:mm:ss', 'en-US');

Will give you: 2017-11-02 11:58:44

Also, if I understand it well the solution for you might be to find the right DB collation. To check the current one run:

SELECT SERVERPROPERTY('collation');

Last thing you can check is SET DATEFORMAT which should change the environment settings for the session.

Upvotes: 2

Related Questions