Geek asks and answers

Creating a User Define Function to Convert Dates to SQL Server

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

Example 1: The following code will format this 20120428 until 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 following code will format this 20120428 until 2012-04-28

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

Thanks for entering!

Guy

+3
source share
1 answer

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

- ( ISDATE() WHERE , ), this , 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

SQL Server 2012 , CASE:

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

( SQL Server 2012 FORMAT(), , , , .)

, , , ( , ). , , inline .

+5

More articles:


All Articles