创建用户定义函数，在SQL Server中转换日期

I know how to convert to date in SQL Server (T-SQL) but how do I create a UDF so that I can call it each time in my code?

我知道如何在SQL Server (T-SQL)中转换到date，但是我如何创建一个UDF，以便每次在代码中调用它?

Example 1: The below code will format this 20120428 to 04/28/2012

示例1:下面的代码将格式化20120428到04/28/2012

SELECT CONVERT(CHAR(10), CONVERT(DATE, TEST_DATE), 101) AS MY_DATE
FROM
MEMBER
WHERE ISDATE(TEST_DATE) <> 0

Example 2: The below code will format this 20120428 to 2012-04-28

示例2:下面的代码将格式化20120428到2012-04-28

SELECT CONVERT(DATE, TEST_DATE) AS MY_DATE
FROM
MEMBER
WHERE ISDATE(TEST_DATE) <> 0

Thank for the input!

感谢输入!

Guy

的家伙

1 个解决方案

#1

For the second example you can do this:

对于第二个例子，你可以这样做:

CREATE FUNCTION dbo.ConvertDate(@d CHAR(10))
RETURNS DATE
AS
BEGIN
    RETURN (SELECT CONVERT(DATE, @d));
END
GO

But a more flexible approach might be:

但更灵活的方法可能是:

CREATE FUNCTION dbo.ConvertRegional
(
  @d CHAR(10),
  @style TINYINT
)
RETURNS CHAR(10)
AS
BEGIN
    RETURN (SELECT CONVERT(CHAR(10), CONVERT(DATE, @d), @style));
END
GO

DECLARE @d CHAR(10);
SELECT @d = '20120428';

SELECT 
 dbo.ConvertDate(@d),
 dbo.ConvertRegional(@d, 101),
 dbo.ConvertRegional(@d, 103),
 dbo.ConvertRegional(@d, 120);

Results:

结果:

----------  ----------  ----------  ----------
2012-04-28  04/28/2012  28/04/2012  2012-04-28

If you don't want to continue filtering out bad non-dates from your source table (keeping the ISDATE() in the WHERE clause should prevent the function from having to deal with those rows), you can change the function this way to avoid errors, if NULL is okay as a substitute:

如果您不想继续从源表中过滤糟糕的非日期(将ISDATE()保留在WHERE子句中，应该可以防止函数处理这些行)，您可以这样修改函数，以避免错误，如果NULL可以作为替代:

CREATE FUNCTION dbo.ConvertRegional
(
  @d CHAR(10),
  @style TINYINT
)
RETURNS CHAR(10)
AS
BEGIN
    RETURN (SELECT CASE WHEN ISDATE(@d) = 1 THEN
      CONVERT(CHAR(10), CONVERT(DATE, @d), @style)
      END);
END
GO

In SQL Server 2012, you can do this instead, which does the same thing without having to write your own CASE:

在SQL Server 2012中，您可以这样做，而不需要编写自己的案例:

CREATE FUNCTION dbo.ConvertRegional
(
  @d CHAR(10),
  @style TINYINT
)
RETURNS CHAR(10)
AS
BEGIN
    RETURN (SELECT CONVERT(CHAR(10), TRY_CONVERT(DATE, @d), @style));
END
GO

(In fact in SQL Server 2012 you can also use FORMAT() so that you don't have to memorize the style numbers, but since I don't know what version you're using I'll leave that for another day.)

(事实上，在SQL Server 2012中，你也可以使用FORMAT()，这样你就不需要记住样式编号了，但是因为我不知道你使用的是什么版本，所以我把它留到以后再说。)

All that said, other than saving a few keystrokes in your queries, this encapsulation is actually going to make your queries perform worse (depending on where they are used). For simple conversions like this it is much better to just perform them inline in most cases.

尽管如此，除了在查询中保存一些击键之外，这种封装实际上会使查询执行得更差(取决于使用的位置)。对于像这样的简单转换，最好是在大多数情况下直接执行它们。

#1