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

Question

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

Navigation

#1 by (5 votes)

4

I've searched Stack Overflow but I can not find it.

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

When I consult 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 I can throw it in the following way:

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

etc.

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

date sql-server-2008 formato

asked by Esteban Jaramillo 18.04.2017 в 21:33

source

1 answer

memory management Error creating effect with css

score 5 · Accepted Answer

You must use a combination of DATENAME and CONVERT (if you had SQL Server 2012 on, 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. p>

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 onwards 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. p>