如何在MS SQL Server 2008上格式化日期 [英] How to format date on MS SQL Server 2008

问题描述

我想根据2015年22月1日或2016年12月15日这样的格式来格式化日期.

I would like to format dates based on a pattern like for example 22/01/2015 or 2016-12-15.

在.NET Framework中，我们有DateTime-> ToString()方法，该方法接受格式作为参数，甚至接受string.Format，其作用相同.

In the .NET Framework we have DateTime -> ToString() method which accepts formats as argument or even string.Format, which does the same.

MS SQL Server 2008上是否有任何基于参数模式格式化日期的功能?

Is there any function on MS SQL Server 2008 that formats date based on a argument pattern?

推荐答案

在SQL 2012中，我遇到了类似的问题，它们有一个很棒的函数，称为format，可以传递日期和所需的格式

I had a similar problem in SQL 2012 they have a great function called format which you could pass in a date and the format you require

SELECT FORMAT(GETDATE(), 'dd/mm/yyy');

在SQL Server 2008中没有类似的东西.我创建了一个简单的函数来完成类似的工作，它可能需要进行调整，但这并不是一个不好的基础.

put there is nothing similar in SQL Server 2008. I created a simple function to do a similar job, it might need tweaking but is not a bad base.

-- =============================================
-- Author:      Luke Mc Redmond
-- =============================================
CREATE FUNCTION [dbo].[FormatDate]
(
-- Add the parameters for the function here
@Date   DATETIME,
@Format NVARCHAR(50)
)
RETURNS NVARCHAR(50)
AS
 BEGIN

   DECLARE @ResultVar nvarchar(50) = UPPER(@Format);


   DECLARE @d nvarchar(20) = CONVERT(nvarchar(20), DATEPART(day, @Date));
   DECLARE @dd nvarchar(20) = CONVERT(nvarchar(20), DATEPART(day, @Date));
   DECLARE @day nvarchar(20) = DATENAME(weekday, @Date);
   DECLARE @ddd nvarchar(20) = DATENAME(weekday, @Date)+' '+CONVERT(nvarchar(5), DATEPART(day, @Date));


   DECLARE @m nvarchar(20) = CONVERT(nvarchar, DATEPART(month, @Date));
   DECLARE @mm nvarchar(20) = CONVERT(nvarchar, DATEPART(month, @Date));
   DECLARE @mmm nvarchar(20) = CONVERT(VARCHAR(3), DATENAME(month, @Date), 100);
   DECLARE @month nvarchar(20) = DATENAME(month, @Date);

   DECLARE @y nvarchar(20) = CONVERT(nvarchar, DATEPART(year, @Date));
   DECLARE @yy nvarchar(20) = RIGHT(CONVERT(nvarchar, DATEPART(year, GETDATE())),2);
   DECLARE @yyy nvarchar(20) = CONVERT(nvarchar, DATEPART(year, @Date));
   DECLARE @yyyy nvarchar(20) = CONVERT(nvarchar, DATEPART(year, @Date));
   DECLARE @year nvarchar(20) = CONVERT(nvarchar, DATEPART(year, @Date));

   SELECT @ResultVar = CASE WHEN CHARINDEX('DAY',@ResultVar) > 0 THEN  REPLACE(@ResultVar collate Latin1_General_CS_AS ,'DAY' collate Latin1_General_CS_AS ,@day)
                        WHEN CHARINDEX('DDD',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS ,'DDD' collate Latin1_General_CS_AS ,@ddd)
                       WHEN CHARINDEX('DD',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS ,'DD' collate Latin1_General_CS_AS ,@dd)
                       WHEN CHARINDEX('D',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS,'D' collate Latin1_General_CS_AS ,@d) END;

   SELECT @ResultVar = REPLACE(@ResultVar collate Latin1_General_CS_AS,'Monday' collate Latin1_General_CS_AS ,'monday')

   SELECT @ResultVar = CASE WHEN CHARINDEX('MONTH',@ResultVar) > 0 THEN  REPLACE(@ResultVar collate Latin1_General_CS_AS,'MONTH' collate Latin1_General_CS_AS,@month)
                       WHEN CHARINDEX('MMM',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS ,'MMM' collate Latin1_General_CS_AS,@mmm)
                       WHEN CHARINDEX('MM',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS,'MM' collate Latin1_General_CS_AS,@mm)
                       WHEN CHARINDEX('M',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS,'M' collate Latin1_General_CS_AS,@m) END;


   SELECT @ResultVar = CASE WHEN CHARINDEX('YEAR',@ResultVar) > 0 THEN  REPLACE(@ResultVar collate Latin1_General_CS_AS,'YEAR' collate Latin1_General_CS_AS ,@year)
                       WHEN CHARINDEX('YYYY',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS,'YYYY' collate Latin1_General_CS_AS ,@yyyy)
                       WHEN CHARINDEX('YYY',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS ,'YYY' collate Latin1_General_CS_AS ,@yyy)
                       WHEN CHARINDEX('YY',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS ,'YY' collate Latin1_General_CS_AS ,@yy)
                       WHEN CHARINDEX('Y',@ResultVar) > 0 THEN REPLACE(@ResultVar collate Latin1_General_CS_AS,'Y' collate Latin1_General_CS_AS ,@y) END;

   SELECT @ResultVar = REPLACE(@ResultVar collate Latin1_General_CS_AS,'monday' collate Latin1_General_CS_AS ,'Monday')

   RETURN @ResultVar

 END;
 GO

希望这会有所帮助！

这篇关于如何在MS SQL Server 2008上格式化日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！