Formatting a Datetime returned from a SQL UD Function

Question

Consider this simple user defined function:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[ufn_GetFirstDayOfMonth] ( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

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

END

which when run using:

SELECT [Business].[dbo].[ufn_GetFirstDayOfMonth] ('03/13/15')

Returns, 2015-03-01 00:00:00.000. Fantastic, exactly what i wanted. Now suppose I wanted to turn things on its head and use the dateformat mdy and execute the functions as:

set dateformat mdy
SELECT [Business].[dbo].[ufn_GetFirstDayOfMonth] ('03/13/15')

why is 2015-03-01 00:00:00.000 (exactly the same as above) returned?

Answer 1

SET dateformat used for parsing dates, not for showing.

Example:

CREATE TABLE #tempTable (DateFormatSample SMALLDATETIME)
SET DATEFORMAT MDY
INSERT INTO #tempTable
VALUES ('09/28/2007')
SET DATEFORMAT YDM
INSERT INTO #tempTable
VALUES ('2007/28/09')
SET DATEFORMAT YMD
INSERT INTO #tempTable
VALUES ('2007/08/28')


SELECT DateFormatSample
FROM #tempTable
DROP TABLE #tempTable

Resultset:

DateFormatSample
———————–
2007-09-28 00:00:00
2007-09-28 00:00:00
2007-08-28 00:00:00

Use Convert function for change date representation.