CODEWITHSUNDEEP
sqlsql-server-2005
user2415378
user2415378

Reputation: 21

convert date to spanish in sql

I am running a report with multiple fields in english. I have 2 date fields at the end of the report, one has to be in English and the other in Spanish. The format the date needs to be in is ie November 1, 2012. I need the spanish to be Novembre 1, 2012. I cannot get the last field of the report to produce in spanish. I am running in sql server 2005.

Upvotes: 2

Views: 12124

Answers (5)

Ethan Shannon
Ethan Shannon

Reputation: 96

You can do the following:

FORMAT(GETDATE(), 'MMMM d,yyyy','es-US')

The last parameter for format datetime is an optional culture parameter that does exactly what you need when passed the right culture.

If you needed to localize to a different language you could find the string to do so from the link below.

https://learn.microsoft.com/en-us/windows-hardware/manufacture/desktop/default-input-locales-for-windows-language-packs?view=windows-11

Upvotes: 0

R.Alonso
R.Alonso

Reputation: 1065

try this:

SELECT CONVERT(VARCHAR(10),GETDATE(), 103)

or

this code, return a VARCHAR(10) with date EN ESPAÑOL, leches.

IDEA (separator used: '-'):

  1. Get format YYYY-MM-DD NVARCHAR(10).
  2. Get format DD-MM-YYYY nvarchar(10)
  3. Use the function

sample:

select dbo.date2txt(GETDATE ())

function to create:

 create function [dbo].[AFecha] (
     @fecha NVARCHAR(10)
 )
 returns NVARCHAR(10)
 as
 begin
 Declare @r nvarchar(10)
 if substring(@Fecha,3,1) = '-'
   set @r = @Fecha
 else
     set @r = substring(@fecha,9,2)+'-'+substring(@fecha,6,2)+'-'+left(@fecha,4)
 Return @r
 End
go



create FUNCTION [dbo].[NTSDate](@fecha DateTime) 
 RETURNS nVarChar(10) AS
  BEGIN
 DECLARE @salida nVarChar(10)
 set @salida =  STR(year(@fecha),4,0) + '-' + RIGHT('0' +  LTRIM(month(@fecha)),2) + '-'  + RIGHT('0' + LTRIM(day(@fecha)),2)
 return (@salida)
 End

go


ALTER function [dbo].[Date2Txt](@Fecha as datetime) returns nvarchar(10)
as
begin
return dbo.afecha(dbo.NTSDate(@Fecha))
end

go

Upvotes: 0

Eli Ekstein
Eli Ekstein

Reputation: 486

This function will translate the month within a string it's based on the sys.syslanguages table.

i.e. SELECT dbo.fn_tranMonth(2,0,'1 déc. 2014 10:26:14 UTC+00:00')

Results:

1 Dec. 2014 10:26:14 UTC+00:00

CREATE FUNCTION [dbo].[Split] (@sep char(1), @s varchar(8000))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT 
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )

GO

CREATE FUNCTION dbo.fn_tranMonth
(
    @fromLan INT
    ,@toLan INT
    ,@string VARCHAR(MAX)
)
RETURNS 
    VARCHAR(50)
AS
BEGIN
    DECLARE @TTTT AS TABLE(PK INT IDENTITY(1,1)
        ,fromMonth VARCHAR(50)
        ,toMonth VARCHAR(50)
        )

    DECLARE 
        @fromMonths VARCHAR(200)
        ,@toMonths VARCHAR(200)
        ,@fromMonth VARCHAR(20)
        ,@toMonth VARCHAR(20)
        ,@rowNum INT=12;

    SELECT @fromMonths=shortmonths
    FROM SYS.syslanguages
    WHERE langid=@fromLan;

    SELECT @toMonths=shortmonths
    FROM sys.syslanguages
    WHERE langid=@toLan;

    INSERT @TTTT(fromMonth)
    SELECT S 
    FROM dbo.Split(',',@fromMonths);

    DECLARE @TTTT2 AS TABLE(PK INT IDENTITY(1,1)
        ,toMonth VARCHAR(50)
        )

    INSERT @TTTT2(toMonth)
    SELECT S 
    FROM dbo.Split(',',@toMonths);

    UPDATE @TTTT
        SET toMonth=B.toMonth
    FROM
        @TTTT A
        JOIN @TTTT2 B ON A.PK=B.PK;

    DECLARE 
        @loopPos INT=0
        ,@returnMonth VARCHAR(50);

    WHILE @loopPos<@rowNum
        BEGIN
            SET @loopPos+=1;
            SELECT 
                @fromMonth=fromMonth
                ,@toMonth=toMonth
            FROM @TTTT
            WHERE PK=@loopPos;

            SET @string=REPLACE(@string,@fromMonth,@toMonth);
        END;

    RETURN @string;
END

Upvotes: 0

Yaroslav
Yaroslav

Reputation: 6544

Maybe is cumbersome, but I don't see how to do it on an easier way.

First, create a function. This function will make use of system view sys.syslanguages to get the correct name of month in spanish. Parameters are a valid date and language (alias on sys.syslanguage view).

CREATE FUNCTION [dbo].[fn_GetMonthName] (
    @Date DATETIME,
    @Language NVARCHAR(100)
)
RETURNS NVARCHAR(400)
AS
BEGIN
    DECLARE @i INT, @m INT,@mlist NVARCHAR(1000)
    SET @m = MONTH(@Date)
    SET @mlist = (SELECT months FROM sys.syslanguages WHERE ALIAS = @language)
    SET @i = 1
    WHILE(@i < @m)
        BEGIN
           SET @mlist = REPLACE(@mlist, SUBSTRING(@mlist,1,CHARINDEX(',',@mlist)) ,'')
           SET @i = @i + 1
        END
    SET @mlist = (CASE CHARINDEX(',',@mlist) WHEN 0 THEN @mlist ELSE SUBSTRING(@mlist,0,CHARINDEX(',',@mlist) ) END )
    RETURN @mlist
END
GO

Then call the function anywhere you need to:

SELECT CONVERT(VARCHAR(20), GETDATE(), 100) AS CurrentDate,
       dbo.fn_GetMonthName (GETDATE(), 'Spanish') AS [Mes-Month]

Result:

      CurrentDate       Mes-Month
 May 24 2013 12:02AM      Mayo

Taken from Get Language specific Month Name from SQL

Upvotes: 2

RandomUs1r
RandomUs1r

Reputation: 4188

Take a look at: http://www.sqlservercurry.com/2010/11/change-default-language-for-sql-server.html

You can temporarily set the language to spanish, not sure how feasible this is for you. The other way to do it would be to write your own months function, and maybe pass it a 2nd parameter that then decides what the output would be.

Upvotes: 0

Related Questions