Home / Questions / 63706
Accepted
SeWaNsX
Asked: 2020-04-19 11:33:41 +0800 CST 2020-04-19 11:33:41 +0800 CST

SQL - Show date in "Mon dd-mm-yyyy" format

  • 772

I've searched on Stack Overflow but can't find it.

In SQL Sever 2008 I have a table called "tbltemp" with a field called "day", this field is type DateTime.

When I query direct dates on this field, it shows the following: Select day from tbltep

2017-03-20 00:00:00.000
2017-03-21 00:00:00.000
2017-03-22 00:00:00.000
2017-03-23 00:00:00.000

I would like to transform the query so that it returns as follows:

Lun 20-03-2017
Mar 21-03-2017
Mie 22-03-2017
Jue 23-03-2017

etc.

(The query I have is much larger than that but I do need to transform the datetime data)

date

1 Answers

  1. Best Answer

    You must use a combination of DATENAMEand CONVERT(if you had SQL Server 2012 or later, this would be easier):

    SET Language 'Spanish';


SELECT  Dia,
        LEFT(DATENAME(WEEKDAY,Dia),3) + ' ' +
        CONVERT(VARCHAR(10),Dia,105) Dia_Formateado
FROM dbo.tbltemp;

    Here is a link with a demo of this code.

    And the results are:

    ╔═════════════════════╦════════════════╗
║         Dia         ║ Dia_Formateado ║
╠═════════════════════╬════════════════╣
║ 20/03/2017 00:00:00 ║ Lun 20-03-2017 ║
║ 21/03/2017 00:00:00 ║ Mar 21-03-2017 ║
║ 22/03/2017 00:00:00 ║ Mié 22-03-2017 ║
║ 23/03/2017 00:00:00 ║ Jue 23-03-2017 ║
╚═════════════════════╩════════════════╝

    And one way to do this in SQL Server 2012 and up is using FORMAT:

    SET Language 'Spanish';

SELECT Dia,
       FORMAT(Dia, 'ddd dd-MM-yyyy') Dia_Formateado
FROM dbo.tbltemp;

    Here is a link with a demo with both versions.

    • 5