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.

回答1:

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

回答2:

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.

回答3:

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

回答4:

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