Format DATETIME SQL Server

Asked

Viewed 1,332 times

4

I wonder if you have any way to change the Datetime format in SQL Server

EX: 2019-07-09 11:42:00 for 09-07-2019 11:42:00

sql sql-server datetime

3 answers

4

Data does not have formats, date is date, has a point in the timeline, only that, formats can be applied in texts that are visual representations for humans to read dates (the same goes for numbers). Then the moment you print some date you can choose the format you want. A shape can be like this:

SELECT FORMAT(data, 'dd-mm-yyyy hh:mm:ss')

I put in the Github for future reference.

3


This is the way to display the date, the database only returns the date, how to display it is done after.

You can format dates in a few different ways on sql-server:

  • Using CAST e CONVERT: format not only dates, but other types as well.
    You can use a direct format, for example:
    SELECT FORMAT(GETDATE(), 'dd-mm-yyyy hh:mm:ss') or a defined format can be used (see the table in the link), for example:
    SELECT CONVERT(varchar, getdate(), 105), which will convert the date to string (varchar) with format style 105.

  • Using SET DATEFORMAT: sets a date format for the current command session, i.e., a sequence of SELECT commands, a STORED PROCEDURE, etc

  • 1

    Note that this is not the format as the date is stored, it is only the default display format.

  • yes vdd got badly written I will correct :)

1

EXEMPLO1 - Direct format, important that the month has to be with upper case, because the result may appear minutes instead of the month

EXEMPLO2 - Displays in the format you need, but the result is a string, can be converted soon after to a datetime (EXEMPLO3). Here is of your choice.

SELECT FORMAT(getdate(), 'dd-MM-yyyy hh:mm:ss') AS EXEMPLO1
     , CONVERT(VARCHAR(30),GETDATE(),105) + SPACE(1) + CONVERT(VARCHAR(30),GETDATE(),108) AS EXEMPLO2
     , CONVERT(DATETIME,CONVERT(VARCHAR(30),GETDATE(),105) + SPACE(1) + CONVERT(VARCHAR(30),GETDATE(),108)) AS EXEMPLO3
     , GETDATE() AS ORIGINAL

Browser other questions tagged sql sql-server datetime

You are not signed in. Login or sign up in order to post.