Generic function which accept all varchar type and return specific datetime format in Sql server

Question

Input:

11-07-2016 14:21:59 
08/07/2016 5:12:52 PM

Output:

2016-07-11 14:21:59
2016-07-08 17:12:52

My date in the format of dd-mm-yyyy hh:mm:ss

The input need to return in specific format as yyyy-mm-dd hh:mm:ss

Please suggest.

Answer 1

Using the below function you can get your date into the specific format:

CREATE FUNCTION [dbo].[fn_ReturnSpecificDateFormat]
(@InputDate VARCHAR (100))
RETURNS VARCHAR(50)
AS
BEGIN

    DECLARE @RetrunFormat AS VARCHAR (19) = '';

    IF CHARINDEX(' ', @InputDate, 1) = 9
        SET @RetrunFormat = CONVERT(VARCHAR(19), CONVERT(DATETIME, @InputDate, 5), 120)
    ELSE
        SET @RetrunFormat = CONVERT(VARCHAR(19), CONVERT(DATETIME, @InputDate, 105), 120) 

    RETURN @RetrunFormat

END

Sample execution with the given sample data:

DECLARE @DateSpecificFormat TABLE (TestDate VARCHAR (100));

INSERT INTO @DateSpecificFormat (TestDate) VALUES
('17/07/16 2:56:20 PM'), 
('11-07-2016 14:21:59'),
('08/07/2016 5:12:52 PM'),
('14-07-2016 05:12:52 PM');

SELECT TestDate, [dbo].[fn_ReturnSpecificDateFormat] (TestDate) AS SpecificFormat
FROM @DateSpecificFormat;

Result:

TestDate                 SpecificFormat
----------------------   --------------------
17/07/16 2:56:20 PM      2016-07-17 14:56:20
11-07-2016 14:21:59      2016-07-11 14:21:59
08/07/2016 5:12:52 PM    2016-07-08 17:12:52
14-07-2016 05:12:52 PM   2016-07-14 17:12:52

This page contains various number of date format.