Why SQL Server CURRENT_TIMESTAMP returning the date/time in 12 hour format

Question

I expect the current time in 24 hour format when I use the following query.

select CURRENT_TIMESTAMP

However, it returns this. (Note that the current time is 6.41PM)

2016-02-24 06:41:10.260

This is a Amazon RDS Sql Server instance.

However, my local server gives the following result, which is I am expecting.

2016-02-24 17:42:41.387

How can I configure the database so that it gives me the time in 24 hour format?

Are you sure your "local" server is in the same timezone as your RDS instance? — , Feb 24 '16 at 06:48
SELECT CASE WHEN Product.LaunchTime < CURRENT_TIMESTAMP THEN 1 ELSE 0 END AS Active FROM Product where Product.ProductID=12345 returns 0 Thus it seems to be nothing to do with the client. Please note that the launch time is 11AM and current time is 6PM — Sapumal Jayaratne, Feb 24 '16 at 06:57
[Sql server does not store date display format.](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) — Zohar Peled, Feb 24 '16 at 07:05

score 0 · Answer 1 · edited Feb 25 '16 at 21:38

0

The Amazon RDS SQL Server database time is fixed to UTC. Even if you are in a different geographical region you cannot change the database time.

Thus, any queries that uses CURRENT_TIMESTAMP needs to be modified.

edited Feb 25 '16 at 21:38

marc_s

answered Feb 24 '16 at 07:10

Sapumal Jayaratne

score 0 · Answer 2 · edited May 09 '19 at 20:26

0

Date format can also be set by using below query:

alter SESSION set NLS_DATE_FORMAT = 'date_format';

e.g.: alter SESSION set NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';

edited May 09 '19 at 20:26

Gryu

answered Feb 24 '16 at 07:44

Mukesh Kalgude

2 Answers2