Technical Article

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

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating