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
