DATEMY - Covnerts and validates MM/YY, MM/YYYY

,

If you've ever needed a convenient and quick way to convert/validate what could possibly be a mm/yy or mm/yyyy date to mm/dd/yyyy (date of month is 01) check out this custom function.  Eg:  select dbo.datemy('04/02') outputs smalldatetime '2002-04-01 00:00:00'.  Select dbo.datemy('04/2002') outputs the same smalldatetime '2002-04-01 00:00:00'.

create function datemy (@strMoYr varchar(20))
returns smalldatetime
as
	begin
	declare @date smalldatetime

/*
Start:
Little routine to double check dates, ken s
20020215
*/
		if isdate(@strMoYr) = 0
		begin
			if len(rtrim(@strMoYr)) <= 5  --potential mm/yy
			begin
				if charindex('/', @strMoYr) <> 0 
				begin
					select 	@strMoYr = substring(@strMoYr,1,charindex('/', @strMoYr)) + 
						'01/' + --inserts a day of month when one is missing, stupid sqlserver
						substring(@strMoYr,charindex('/',@strMoYr)+1,2)
				end
				else
				begin
					if charindex('-', @strMoYr) <> 0
					begin
						select 	@strMoYr = substring(@strMoYr,1,charindex('-', @strMoYr)) + 
							'01-' + --inserts a day of month when one is missing, stupid sqlserver
							substring(@strMoYr,charindex('-',@strMoYr)+1,2)
					end
				end
			end
--comment, handles 2 and 4 digit years.  If user mixes delimiters, too bad.  Ken
			else --date too long for two digit year
				if right(rtrim(@strMoYr),4) between 1900 and 2100
				begin
					if charindex('/', @strMoYr) <> 0
					begin
						select 	@strMoYr = substring(@strMoYr,1,charindex('/', @strMoYr)) + 
							'01/' + --inserts a day of month when one is missing, stupid sqlserver
							substring(@strMoYr,charindex('/',@strMoYr)+1,4)
					end
					if charindex('-', @strMoYr) <> 0
					begin
						select 	@strMoYr = substring(@strMoYr,1,charindex('-', @strMoYr)) + 
							'01-' + --inserts a day of month when one is missing, stupid sqlserver
								substring(@strMoYr,charindex('-',@strMoYr)+1,4)
					end
				end
		end		
		else
		select @date=@strMoYr
		begin
			if isdate(@strMoYr) = 0
			begin
			select @strMoYr = NULL
			select @date = NULL
			end
		end
/*
Finish:
Little routine to double check dates, ken s
20020215
*/
	return(@strMoYr)
	end

Rate

Share

Share

Rate