Microsoft SQL Server
Скрипты
T-SQL

Работа строкой, содержащей дату в разном формате

Опубликовано: 15 май 08
Рейтинг:

Автор: Petr Chulkov
Прислал: Petr Chulkov

1-я функция.
работает со строками, содержащими дату в форматах
mmm%dd, yyyy , например jan 01, 2004 , март 01, 2004 и т.д.


DD.MM.YYYY
DD.MM.YY
DD.MM.20YY
D.MM.20YY
MM.20YY - в этом случае число подставляется из GetDate()
DD.MM
D.MM

CREATE FUNCTION [dbo].[GetDateFromStr]  ( @s NVarChar(Max) ) 
RETURNS DateTime 
AS BEGIN
	Declare @Result datetime
	declare @MnthStart int , @MnthEnd int 
	declare @day as nvarchar(10),@month as nvarchar(10),@year as nvarchar(10)
	Declare @SDay int, @SMonth int, @SYear int
	Select @SYear=SYear, @SMonth=SMonth, @SDay=SDay from Dictionary.dbo.getsysdate

	set @MnthStart= PATINDEX('%[яфмаисондjfmajsond][неапаювекоеapaueco][врйнгтякnbryngptvc]%[- ,.][0-9][0-9][- ,.][- ,.][0-9][0-9]%', @s ) 

	if @MnthStart > 0 -- дата формата mmm%dd, yyyy .... надо чуток переставить
		select @s = 
				substring(@s , 1, @MnthStart-1 ) + ' ' +
				substring(@s , @MnthStart+4, 2 ) + ' ' +
				substring(@s , @MnthStart  , 3 ) + ' ' +
				substring(@s , @MnthStart+8, 4 ) + ' ' +
				substring(@s , PATINDEX('%[- ,.][0-9][0-9][- ,.][- ,.][0-9][0-9]%', @s )+9  , len(@s)-@MnthStart )

	if PATINDEX('%[. ,]янв%', @s ) + PATINDEX('%[. ,]jan%', @s ) > 0  begin
		select @MnthStart = PATINDEX('%[. ,]янв%', @s )+PATINDEX('%[. ,]jan%', @s )+1 -- as qq
		if patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
		else select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '01' )
	end 
	if PATINDEX('%[. ,]фев%', @s ) + PATINDEX('%[. ,]feb%', @s ) > 0  begin
		select @MnthStart = PATINDEX('%[. ,]фев%', @s )+PATINDEX('%[. ,]feb%', @s )+1 -- as qq
		select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		if patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
		else select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '02' )
	end 
	if PATINDEX('%[. ,]мар%', @s ) + PATINDEX('%[. ,]mar%', @s ) > 0  begin
		select @MnthStart = PATINDEX('%[. ,]мар%', @s ) + PATINDEX('%[. ,]mar%', @s )+1 -- as qq
		select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		if patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
		else select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '03' )
	end 
	if PATINDEX('%[. ,]апр%', @s ) + PATINDEX('%[. ,]apr%', @s ) > 0  begin
		select @MnthStart = PATINDEX('%[. ,]апр%', @s ) + PATINDEX('%[. ,]apr%', @s )+1 -- as qq
		select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		if patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
		else select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '04' )
	end 
	if PATINDEX('%[. ,]май%', @s ) + PATINDEX('%[. ,]may%', @s ) > 0  begin
		select @MnthStart = PATINDEX('%[. ,]май%', @s ) + PATINDEX('%[. ,]may%', @s )+1 -- as qq
		select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		if patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
		else select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '05' )
	end 
	if PATINDEX('%[. ,]июн%', @s ) + PATINDEX('%[. ,]jun%', @s ) > 0  begin
		select @MnthStart = PATINDEX('%[. ,]июн%', @s ) + PATINDEX('%[. ,]jun%', @s )+1 -- as qq
		select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		if patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
		else select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '06' )
	end 
	if PATINDEX('%[. ,]июн%', @s ) + PATINDEX('%[. ,]jun%', @s ) > 0  begin
		select @MnthStart = PATINDEX('%[. ,]июл%', @s ) + PATINDEX('%[. ,]jul%', @s )+1 -- as qq
		select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		if patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
		else select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '07' )
	end 
	if PATINDEX('%[. ,]авг%', @s ) + PATINDEX('%[. ,]aug%', @s ) > 0  begin
		select @MnthStart = PATINDEX('%[. ,]авг%', @s ) + PATINDEX('%[. ,]aug%', @s )+1 -- as qq
		select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		if patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
		else select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '08' )
	end 
	if PATINDEX('%[. ,]сен%', @s ) + PATINDEX('%[. ,]sep%', @s ) > 0  begin
		select @MnthStart = PATINDEX('%[. ,]сен%', @s ) + PATINDEX('%[. ,]sep%', @s )+1 -- as qq
		select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		if patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
		else select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '09' )
	end 
	if PATINDEX('%[. ,]окт%', @s ) + PATINDEX('%[. ,]oct%', @s ) > 0  begin
		select @MnthStart = PATINDEX('%[. ,]окт%', @s ) + PATINDEX('%[. ,]oct%', @s )+1 -- as qq
		select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1	
		if patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
		else select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '10' )
	end 
	if PATINDEX('%[. ,]ноя%', @s ) + PATINDEX('%[. ,]nov%', @s ) > 0  begin
		select @MnthStart = PATINDEX('%[. ,]ноя%', @s ) + PATINDEX('%[. ,]nov%', @s )+1 -- as qq
		select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		if patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
		else select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '11' )
	end 
	if PATINDEX('%[. ,]дек%', @s ) + PATINDEX('%[. ,]dec%', @s ) > 0  begin
		select @MnthStart = PATINDEX('%[. ,]дек%', @s ) + PATINDEX('%[. ,]dec%', @s )+1 -- as qq
		select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		if patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
		else select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
		set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '12' )
	end 


	-- select @s as [input string] 
	if PATINDEX('%[0-3][0-9][. ,][0-1][0-9][. ,][0-9][0-9][0-9][0-9]%' , @s)   > 0 begin -- DD.MM.YYYY
		-- print 'DD.MM.YYYY'
		set @MnthStart = PATINDEX('%[0-3][0-9][. ,][0-1][0-9][. ,][0-9][0-9][0-9][0-9]%' , @s) 
		select	@day = substring(@s , @MnthStart   , 2) , 
				@month = substring(@s , @MnthStart+3 , 2) , 
				@year = substring(@s , @MnthStart +6  , 4) 
	end else 
	if PATINDEX('%[0-3][0-9][. ,][0-1][0-9][. ,][0-9][0-9]%' , @s)   > 0 begin -- DD.MM.YY
		-- print 'DD.MM.YY'
		set @MnthStart = PATINDEX('%[0-3][0-9][. ,][0-1][0-9][. ,][0-9][0-9]%' , @s) 
		select @day = substring(@s , @MnthStart   , 2) , @month = substring(@s , @MnthStart+3 , 2) , @year = '20'+substring(@s , @MnthStart +6  , 2) 
	end else 
	if PATINDEX('%[0-3][0-9][. ,][0-1][0-9][. ,]20[0-9][0-9]%' , @s)   > 0 begin -- DD.MM.20YY
		-- print 'DD.MM.20YY'
		set @MnthStart = PATINDEX('%[0-3][0-9][. ,][0-1][0-9][. ,]20[0-9][0-9]%' , @s) 
		select @day = substring(@s , @MnthStart   , 2) , @month = substring(@s , @MnthStart+3   , 2) , @year = substring(@s , @MnthStart +6  , 4) 
	end else 
	if PATINDEX('%[1-9][. ,][0-9][0-9][. ,]20[0-9][0-9]%' , @s)   > 0 begin -- D.MM.20YY
		-- print 'D.MM.20YY'
		set @MnthStart = PATINDEX('%[0-9][. ,][0-9][0-9][. ,]20[0-9][0-9]%' , @s) 
		select @day = substring(@s , @MnthStart   , 1) , @month = substring(@s , @MnthStart+2   , 2) , @year = substring(@s , @MnthStart +5  , 4) 
	end else 
	if PATINDEX('%[0-2][0-9][. ,]20[0-9][0-9]%' , @s)   > 0 begin -- MM.20YY
		-- print 'MM.20YY'
		set @MnthStart = PATINDEX('%[0-2][0-9][. ,]20[0-9][0-9]%' , @s) 
		select @day = cast( day(getdate()) as nvarchar(2)) 
			 , @month = substring(@s , @MnthStart   , 2)
			 , @year = substring(@s , @MnthStart+3  , 4)
	end else if PATINDEX('%[0-3][0-9][. ,][0-9][0-9]%' , @s)   > 0 begin -- DD.MM
		-- print 'DD.MM'
		set @MnthStart = PATINDEX('%[0-9][0-9][. ,][0-9][0-9]%' , @s) 
		select @day = substring(@s , @MnthStart   , 2) , @month = substring(@s , @MnthStart+3   , 2) , @year = cast(year(GetDate()) as nvarchar(4))
	end else if PATINDEX('%[1-9][. ,][0-1][0-9]%' , @s)   > 0 begin -- D.MM
		-- print 'D.MM'
		set @MnthStart = PATINDEX('%[1-9][. ,][0-1][0-9]%' , @s) 
		select @day = substring(@s , @MnthStart   , 1) , @month = substring(@s , @MnthStart+2   , 2) , @year = cast(year(GetDate()) as nvarchar(4))
	end 
	select @Result = convert(datetime, @day +'.'+@month+'.'+@year , 104)
	RETURN @Result
END


и 2-й вариант - вариации на тему 1-й функции, но работает с строками, начинающимися с месяца и года, но не содержащие число..
нужное число передаётся ещё одним параметром... так же добавлена поддержка формата даты только из месяца (год подставляется текущий.. хотя это спорно, т.к. зависит от конкретной задачи)....


Create FUNCTION [dbo].[GetDateFromStr2] ( @s NVarChar(Max) , @DefaultDay as nvarchar(2) )
	RETURNS DateTime AS BEGIN
Declare @Result datetime
declare @MnthStart int , @MnthEnd int 
declare @day as nvarchar(10),@month as nvarchar(10),@year as nvarchar(10)
Declare @SDay int, @SMonth int, @SYear int
Select	@SYear	= SYear, @SMonth	= SMonth, @SDay	= SDay from Dictionary.dbo.getsysdate

set @MnthStart= PATINDEX('%[яфмаисондjfmajsond][неапаювекоеapaueco][врйнгтякnbryngptvc]%[- ,.][0-9][0-9][- ,.][- ,.][0-9][0-9]%', @s ) 

if @MnthStart > 0 -- дата формата mmm%dd, yyyy .... надо чуток переставить
	select @s = 
			substring(@s , 1, @MnthStart-1 ) + ' ' +
			substring(@s , @MnthStart+4, 2 ) + ' ' +
			substring(@s , @MnthStart  , 3 ) + ' ' +
			substring(@s , @MnthStart+8, 4 ) + ' ' +
			substring(@s , PATINDEX('%[- ,.][0-9][0-9][- ,.][- ,.][0-9][0-9]%', @s )+9  , len(@s)-@MnthStart )

if -- если дата всё ещё начинается с месяца .... 
		PATINDEX('%янв%', @s ) = 1 or PATINDEX('%jan%', @s ) = 1 or
		PATINDEX('%фев%', @s ) = 1 or PATINDEX('%feb%', @s ) = 1 or
		PATINDEX('%мар%', @s ) = 1 or PATINDEX('%mar%', @s ) = 1 or
		PATINDEX('%апр%', @s ) = 1 or PATINDEX('%apr%', @s ) = 1 or
		PATINDEX('%май%', @s ) = 1 or PATINDEX('%may%', @s ) = 1 or
		PATINDEX('%июн%', @s ) = 1 or PATINDEX('%jun%', @s ) = 1 or
		PATINDEX('%июн%', @s ) = 1 or PATINDEX('%jun%', @s ) = 1 or
		PATINDEX('%авг%', @s ) = 1 or PATINDEX('%aug%', @s ) = 1 or
		PATINDEX('%сен%', @s ) = 1 or PATINDEX('%sep%', @s ) = 1 or
		PATINDEX('%окт%', @s ) = 1 or PATINDEX('%oct%', @s ) = 1 or
		PATINDEX('%ноя%', @s ) = 1 or PATINDEX('%nov%', @s ) = 1 or
		PATINDEX('%дек%', @s ) = 1 or PATINDEX('%dec%', @s ) = 1
	set @s = @DefaultDay + ' ' +  @s
-- select @s as qqq
if isnull(PATINDEX('%[. ,]янв%', @s ),0) + isnull(PATINDEX('%[. ,]jan%', @s ),0) > 0  begin
	select @MnthStart = PATINDEX('%[. ,]янв%', @s )+PATINDEX('%[. ,]jan%', @s )+1 -- as qq
	if patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 
		select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
	else 
		select @MnthEnd = patindex('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '01' )
end 
if IsNull( PATINDEX('%[. ,]фев%', @s ),0) + IsNull( PATINDEX('%[. ,]feb%', @s ),0) > 0  begin
	select @MnthStart = IsNull( PATINDEX('%[. ,]фев%', @s ),0)+IsNull( PATINDEX('%[. ,]feb%', @s ),0)+1 -- as qq
	select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	if PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 
		select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
	else
		select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '02' )
end 
if IsNull( PATINDEX('%[. ,]мар%', @s ),0) + IsNull( PATINDEX('%[. ,]mar%', @s ),0) > 0  begin
	select @MnthStart = IsNull( PATINDEX('%[. ,]мар%', @s ),0) + IsNull( PATINDEX('%[. ,]mar%', @s ),0)+1 -- as qq
	select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	if PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 
		select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
	else 
		select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '03' )
end 
if IsNull( PATINDEX('%[. ,]апр%', @s ),0) + IsNull( PATINDEX('%[. ,]apr%', @s ),0) > 0  begin
	select @MnthStart = IsNull( PATINDEX('%[. ,]апр%', @s ),0) + IsNull( PATINDEX('%[. ,]apr%', @s ),0)+1 -- as qq
	select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	if PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 
		select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
	else 
		select @MnthEnd =  PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '04' )
end 
if IsNull( PATINDEX('%[. ,]май%', @s ),0) + IsNull( PATINDEX('%[. ,]may%', @s ),0) > 0  begin
	select @MnthStart = IsNull( PATINDEX('%[. ,]май%', @s ),0) + IsNull( PATINDEX('%[. ,]may%', @s ),0)+1 -- as qq
	select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	if  PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 
		select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
	else 
		select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '05' )
end 
if IsNull( PATINDEX('%[. ,]июн%', @s ) ,0) + IsNull( PATINDEX('%[. ,]jun%', @s ) ,0)> 0  begin
	select @MnthStart = IsNull( PATINDEX('%[. ,]июн%', @s ),0) + IsNull( PATINDEX('%[. ,]jun%', @s ),0)+1 -- as qq
	select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	if PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 
		select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
	else
		select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '06' )
end 
if IsNull( PATINDEX('%[. ,]июн%', @s ),0) + IsNull( PATINDEX('%[. ,]jun%', @s ),0) > 0  begin
	select @MnthStart = IsNull( PATINDEX('%[. ,]июл%', @s ),0) + IsNull( PATINDEX('%[. ,]jul%', @s ),0)+1 -- as qq
	select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	if PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 
		select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
	else
		select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '07' )
end 
if IsNull( PATINDEX('%[. ,]авг%', @s ),0) + IsNull( PATINDEX('%[. ,]aug%', @s ),0) > 0  begin
	select @MnthStart = IsNull( PATINDEX('%[. ,]авг%', @s ),0) + IsNull( PATINDEX('%[. ,]aug%', @s ),0)+1 -- as qq
	select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	if PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 
		select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
	else 
		select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '08' )
end 
if IsNull( PATINDEX('%[. ,]сен%', @s ),0) + IsNull( PATINDEX('%[. ,]sep%', @s ),0) > 0  begin
	select @MnthStart = IsNull( PATINDEX('%[. ,]сен%', @s ),0) + IsNull( PATINDEX('%[. ,]sep%', @s ),0)+1 -- as qq
	select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	if PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0
		select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
	else
		select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '09' )
end 
if IsNull( PATINDEX('%[. ,]окт%', @s ),0) + IsNull( PATINDEX('%[. ,]oct%', @s ),0) > 0  begin
	select @MnthStart = IsNull( PATINDEX('%[. ,]окт%', @s ),0) + IsNull( PATINDEX('%[. ,]oct%', @s ),0)+1 -- as qq
	select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1	
	if PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0
		select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
	else
		select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '10' )
end 
if IsNull( PATINDEX('%[. ,]ноя%', @s ),0) + IsNull( PATINDEX('%[. ,]nov%', @s ) ,0) > 0  begin
	select @MnthStart = IsNull( PATINDEX('%[. ,]ноя%', @s ),0) + IsNull( PATINDEX('%[. ,]nov%', @s ),0)+1 -- as qq
	select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	if PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0
		select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
	else 
		select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '11' )
end 
if IsNull( PATINDEX('%[. ,]дек%', @s ),0) + IsNull( PATINDEX('%[. ,]dec%', @s ),0)  > 0  begin
	select @MnthStart = IsNull( PATINDEX('%[. ,]дек%', @s ),0) + IsNull( PATINDEX('%[. ,]dec%', @s ),0)+1 -- as qq
	select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	if PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) ) <= 0 
		select @MnthEnd = len(substring(@s , @MnthStart, 20 ))
	else 
		select @MnthEnd = PATINDEX('%[. ,]%' , substring(@s , @MnthStart, 20 ) )-1
	set @s = replace( @s , substring(@s , @MnthStart , @MnthEnd) , '12' )
end 

-- select @s as [input string] 
if PATINDEX('%[0-3][0-9][. ,][0-1][0-9][. ,][0-9][0-9][0-9][0-9]%' , @s)   > 0 begin -- DD.MM.YYYY
	-- print 'DD.MM.YYYY'
	set @MnthStart = PATINDEX('%[0-3][0-9][. ,][0-1][0-9][. ,][0-9][0-9][0-9][0-9]%' , @s) 
	select @day = substring(@s , @MnthStart   , 2) , @month = substring(@s , @MnthStart+3 , 2) , @year = substring(@s , @MnthStart +6  , 4) 
end else 
if PATINDEX('%[0-3][0-9][. ,][0-1][0-9][. ,][0-9][0-9]%' , @s)   > 0 begin -- DD.MM.YY
	-- print 'DD.MM.YY'
	set @MnthStart = PATINDEX('%[0-3][0-9][. ,][0-1][0-9][. ,][0-9][0-9]%' , @s) 
	select @day = substring(@s , @MnthStart   , 2) , @month = substring(@s , @MnthStart+3 , 2) , @year = '20'+substring(@s , @MnthStart +6  , 2) 
end else 
if PATINDEX('%[0-3][0-9][. ,][0-1][0-9][. ,]20[0-9][0-9]%' , @s)   > 0 begin -- DD.MM.20YY
	-- print 'DD.MM.20YY'
	set @MnthStart = PATINDEX('%[0-3][0-9][. ,][0-1][0-9][. ,]20[0-9][0-9]%' , @s) 
	select @day = substring(@s , @MnthStart   , 2) , @month = substring(@s , @MnthStart+3   , 2) , @year = substring(@s , @MnthStart +6  , 4) 
end else 
if PATINDEX('%[1-9][. ,][0-9][0-9][. ,]20[0-9][0-9]%' , @s)   > 0 begin -- D.MM.20YY
	-- print 'D.MM.20YY'
	set @MnthStart = PATINDEX('%[0-9][. ,][0-9][0-9][. ,]20[0-9][0-9]%' , @s) 
	select @day = substring(@s , @MnthStart   , 1) , @month = substring(@s , @MnthStart+2   , 2) , @year = substring(@s , @MnthStart +5  , 4) 
end else 
if PATINDEX('%[0-2][0-9][. ,]20[0-9][0-9]%' , @s)   > 0 begin -- MM.20YY
	-- print 'MM.20YY'
	set @MnthStart = PATINDEX('%[0-2][0-9][. ,]20[0-9][0-9]%' , @s) 
	select @day = @DefaultDay 
		 , @month = substring(@s , @MnthStart   , 2)
		 , @year = substring(@s , @MnthStart+3  , 4)
end else if PATINDEX('%[0-3][0-9][. ,][0-9][0-9]%' , @s)   > 0 begin -- DD.MM
	-- print 'DD.MM'
	set @MnthStart = PATINDEX('%[0-9][0-9][. ,][0-9][0-9]%' , @s) 
	select @day = substring(@s , @MnthStart   , 2) , @month = substring(@s , @MnthStart+3   , 2) , @year = cast(year(GetDate()) as nvarchar(4))
end else if PATINDEX('%[1-9][. ,][0-1][0-9]%' , @s)   > 0 begin -- D.MM
	-- print 'D.MM'
	set @MnthStart = PATINDEX('%[1-9][. ,][0-1][0-9]%' , @s) 
	select @day = substring(@s , @MnthStart   , 1) , @month = substring(@s , @MnthStart+2   , 2) , @year = cast(year(GetDate()) as nvarchar(4))
end else if PATINDEX('%[0-1][0-9]%' , @s)   > 0 begin -- MM
	-- print 'MM'
	set @MnthStart = PATINDEX('%[0-1][0-9]%' , @s) 
	select @day = @DefaultDay , @month = substring(@s , @MnthStart   , 2) , @year = cast(year(GetDate()) as nvarchar(4))
end 

	select @Result = convert(datetime, @day +'.'+@month+'.'+@year , 104)
RETURN @Result
END


данные функции не претендуют на оптимальность, но они работают.

Комментарии




Необходимо войти на сайт, чтобы оставлять комментарии

Раздел FAQ: Microsoft SQL Server / Скрипты / T-SQL / Работа строкой, содержащей дату в разном формате