In the actual processing, there is a more alternative date plus and minus processing, that is, in the specified date, plus (or minus) multiple date parts, such as March 11, 2005, plus 1 years 3 months 11 days 2 hours. The power of the DateAdd function seems a bit inadequate for this date-adding and subtraction process.
To achieve multiple date parts of the addition and subtraction processing, the most important thing is to reduce the date character decomposition, and then according to the result of the decomposition of the corresponding date in the specified date part of the corresponding value, the difficulty lies in how to decompose the date characters, and to determine the date of the decomposition of the date part To successfully decompose the date characters, you first need to specify the date and subtraction date character format, you can define this:
Y-m-d H:M:S.M | -y-m-d H:M:S.M
Copy Code code as follows:
Note: Date characters are entered in the same way as date strings to be added. The date and time part is separated by a space, the first character if is the minus sign (-), the expression does the subtraction processing, otherwise does the addition processing. If a date character contains only numbers, it is considered a date character and contains only the information for the day.
After the date character format is determined, the processing method can be determined: get the first character of the date character, judge the processing mode, and then the date character will be added to the date and time two parts by space, for the date part from the low to high level of the date data processing, for time from high to low to deal with one by one.
The following are the user-defined function codes that are processed at the same time as multiple parts of the implementation date:
Copy Code code as follows:
CREATE FUNCTION F_dateadd (
@Date datetime,
@DateStr varchar (23)
) RETURNS datetime
As
BEGIN
DECLARE @bz int,@s varchar (), @i int
IF @DateStr is null OR @Date is null
OR (CHARINDEX ('. ', @DateStr) >0
and @DateStr not like '%[:]%[:]%.% ')
Return (NULL)
IF @DateStr = ' return (@Date)
SELECT @bz =case
When left (@DateStr, 1) = '-' THEN-1
ELSE 1 End,
@DateStr =case
When left (@Date, 1) = '-'
THEN STUFF (RTRIM (LTRIM (@DateStr)), 1, 1, ' ")
ELSE RTRIM (LTRIM (@DateStr)) end
IF CHARINDEX (', @DateStr) >1
OR CHARINDEX ('-', @DateStr) >1
OR (CHARINDEX ('. ', @DateStr) =0
and CHARINDEX (': ', @DateStr) =0)
BEGIN
SELECT @i=charindex (', @DateStr + ')
, @s=reverse (Left (@DateStr, @i-1)) + '-'
, @DateStr =stuff (@DateStr, 1,@i, ")
, @i=0
While @s> ' and @i<3
SELECT @Date =case @i
When 0 THEN DATEADD (day, @bz *reverse (left (@s,charindex ('-', @s)-1), @Date)
When 1 THEN DATEADD (Month, @bz *reverse (left (@s,charindex ('-', @s)-1), @Date)
When 2 THEN DATEADD (year, @bz *reverse (left (@s,charindex ('-', @s)-1), @Date)
End,
@s=stuff (@s,1,charindex ('-', @s), '),
@i=@i+1
End
IF @DateStr > '
BEGIN
IF CHARINDEX ('. ', @DateStr) >0
SELECT @Date =dateadd (millisecond
, @bz *stuff (@DateStr, 1,charindex ('. ', @DateStr), "),
@Date),
@DateStr =left (@DateStr, CHARINDEX ('. ', @DateStr)-1) + ': ',
@i=0
ELSE
SELECT @DateStr = @DateStr + ': ', @i=0
While @DateStr > ' and @i<3
SELECT @Date =case @i
When 0 THEN DATEADD (Hour, @bz *left (@DateStr, CHARINDEX (': ', @DateStr)-1), @Date)
When 1 THEN DATEADD (Minute, @bz *left (@DateStr, CHARINDEX (': ', @DateStr)-1), @Date)
When 2 THEN DATEADD (Second, @bz *left (@DateStr, CHARINDEX (': ', @DateStr)-1), @Date)
End,
@DateStr =stuff (@DateStr, 1,charindex (': ', @DateStr), '),
@i=@i+1
End
Return (@Date)
End
