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.
No comments:
Post a Comment