How to get the correct date format?-CodePudding

Not being able to get the desired output. The question is : Create a function that takes an input parameter type datetime and returns the date in the format MM/DD/YYYY. For example if I pass in ‘2006-11-21 23:34:05.920’, the output of the functions should be 11/21/2006

My SQL code :

CREATE FUNCTION CalculateDate(@InputDate DATETIME)
RETURNS DATE 
AS
BEGIN
    RETURN CONVERT(varchar(10), @InputDate, 101)
END

SELECT dbo.CalculateDate('2006-11-21 23:34:05.920') AS [MM/DD/YYYY]

But on execution :

CodePudding user response：

A date does not have a format. 2006-11-21, 21.11.2006, 11/21/2006 are all representations of the same date (or can be).

When you are asked to write a function that returns a date in a certain format, you are asked to return a string. Your function, however, returns a DATE.

What happens in your function is that you convert the datetime to a date string and the DBMS converts that back to a DATE.

Change REURNS DATE to `RETURNS VARCHAR(10) hence:

CREATE FUNCTION CalculateDate(@InputDate DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
    RETURN CONVERT(varchar(10), @InputDate, 101)
END

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f5cfdc7e1761ae6e370a6b4f51674813