Function to Format Date in SQL QUERY

[dhilipkumar]

The code sample combines built in sql functions - YEAR(), MONTH() and DAY() - to format a given date as YYYY/MM/DD .

Example: output will be of the form 2008/7/01


CREATE FUNCTION [dbo].[fn_GetDate] ( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

    RETURN CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +
                CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/' +
                CAST(DAY(@pInputDate) AS VARCHAR(2)) AS DATETIME)

END
GO   


To be callable from SQL expressions, a user-defined function mus:

• Be a stroed function
  
  Accept only IN parameters
  
  Accept only valid SQL data types, not PL/SQL specific types, as parameters
  
  Return data types that are valid SQL data types, not PLSQL specific types