Convert varchar to datetime and vice versa in MS SQL

Sometimes it is necessary to convert string date to datetime type and vice versa. In TSQL is a function, which allows you to perform such a transformation. For example, user defined conversion function of any value with datetime type to the corresponding string YYYY-MM-DD HH:MM:SS, might look like this:

CREATE FUNCTION [dbo].[dt_to_str] 
(
@dt datetime
)
RETURNS varchar(19)
AS
BEGIN
RETURN CONVERT(varchar(19), @dt, 120)
END

However, using CONVERT function to convert string to datetime is not safe, especially if you plan to deploy database on several computers with different settings. A situation may araise when this transformation is completed with an error because of incorrect format of date.
A safer way to convert - using dateadd function. It is known that the datetime is counted from January 1, 1900. This date corresponds to the zero value, check out:

SELECT cast(0 as datetime)

So, finding the difference from the zero date and adding it to her we can get a string as a datetime. Below is a simple example:

ALTER FUNCTION [dbo].[make_date] 
(
@day int,
@month int,
@year int
)
RETURNS datetime
AS
BEGIN
DECLARE @dt datetime
SET @dt = cast(0 as datetime)

-- add years
SELECT @dt = dateadd(year, (@year - 1900), @dt)
-- add months
SELECT @dt = dateadd(month, (@month - 1), @dt)
-- add days
SELECT @dt = dateadd(day, (@day - 1), @dt)

RETURN @dt
END

This function takes as integer variables day, month and year, and returns appropriate datetime value.

Now consider the combined sample with strings and integers. Now the date and time will be presented in the form of the long integer format YYYYMMDDHHII. The transformation function will look like:

ALTER FUNCTION [dbo].[int_to_datetime] 
(
@val bigint
)
RETURNS datetime
AS
BEGIN
DECLARE @str varchar(12)
SET @str = CAST(@val AS varchar)

IF LEN(@str) < 12 -- wrong format
OR CAST(SUBSTRING(@str, 5, 2) AS int) > 12 -- wrong month
OR CAST(SUBSTRING(@str, 7, 2) AS int) > 31 -- wrong day
OR CAST(SUBSTRING(@str, 9, 2) AS int) > 23 -- wrong hour
OR CAST(SUBSTRING(@str, 11, 2) AS int) > 60 -- wrong minute
RETURN NULL

DECLARE @dt datetime
SET @dt = cast(0 as datetime)

-- get year
SELECT @dt = dateadd(year, (SUBSTRING(@str, 1, 4) - 1900), @dt)
-- get month
SELECT @dt = dateadd(month, (SUBSTRING(@str, 5, 2) - 1), @dt)
-- get day
SELECT @dt = dateadd(day, (SUBSTRING(@str, 7, 2) - 1), @dt)
-- get hour
SELECT @dt = dateadd(hour, CAST(SUBSTRING(@str, 9, 2) AS int), @dt)
-- get minute
SELECT @dt = dateadd(minute, CAST(SUBSTRING(@str, 11, 2) AS int), @dt)

RETURN @dt
END

This conversion executes quickly and you don't depend on the date format settings.