Problem with a Stored Procedure (Warning: This is Long)

[fattyfatpants]

I apologize for the length of this post but I need some advice on how to shorten it and make it more efficient...also please note the bolded text towards the bottom of the code, after looking over the procedure will that do what I'm wanting it to do (convert the string value to a datetime)

--This procedure takes 4 input parameters to work as 2 seperate date ranges...a date range for a Period between 06/01/2003 to 04/30/2004 and for a Billing Period between 06/01/2003 to 04/30/2004
--For some reason the database developer(s) created the fields as 4 character string data type as opposed to date data type and the majority of this procedure will take the date ranges passed in and convert each of them to date data type for correct selection

use son_db
go

CREATE PROC	dbo.date_range
@beginperiodrange varchar(4), --variable to hold the beginning period range to look in...input value
@endperiodrange varchar(4), --variable to hold the ending period range to look in...input value
@beginbillperiodrange varchar(4), --variable to hold the beginning billing period range to look in...input value
@endbillperiodrange varchar(4), --variable to hold the ending billing period range to look in...input value

AS

DECLARE @yr char(2), --variable to hold a 2 digit year value
@mn char(2), --variable to hold a 2 digit month value
@dy char(2), --variable to hold a day value
@BeginPeriod varchar(10), --variable to hold the concatenated beginning Period value in a short date format (mm/dd/yyyy)
@EndPeriod varchar(10), --variable to hold the concatenated ending period value in a short date format (mm/dd/yyyy)
@BeginBillPer varchar(10), --variable to hold the concatenated beginning Billing Period value in a short date format (mm/dd/yyyy)
@EndBillPer varchar(10) --variable to hold the concatenated ending billing period value in a short date format (mm/dd/yyyy)

set @yr = right(@beginperiodrange, 2)
set @mn = left(@beginperiodrange, 2)
set @dy = '01'

--will concatenate to look like 06/01/2003
set @BeginPeriod = @mn + '/' + @dy + '/' + '20' + @yr

set @yr = right(@endperiodrange, 2)
set @mn = left(@endperiodrange, 2)
set @dy = '30'

--will concatenate to look like 04/30/2004
set @EndPeriod = @mn + '/' + @dy + '/' + '20' + @yr

set @yr = right(@beginbillperiodrange, 2)
set @mn = left(@beginbillperiodrange, 2)
set @dy = '01'

--will concatenate to look like 06/01/2003
set @BeginBillPer = @mn + '/' + @dy + '/' + '20' + @yr

set @yr = right(@endbillperiodrange, 2)
set @mn = left(@endbillperiodrange, 2)
set @dy = '30'

--will concatenate to look like 04/30/2003
set @EndBillPer = @mn + '/' + @dy + '/' + '20' + @yr

--the fields in the query these are being evaluated against are cost.cbiper (Billing Period) and periodt.pe (Period)

SELECT client.claddr1, matter.mmatter, matter.morgaty, client.crelated, matter.mjnum, cost.cbiper, periodt.pe, periodt.peendt, cost.camount, cost.cbillamt, matths.mhdobidb, matths.mhdowkdb
FROM   son_db.dbo.periodt periodt INNER JOIN (((son_db.dbo.client client INNER JOIN son_db.dbo.matter matter ON client.clnum=matter.mclient) INNER JOIN son_db.dbo.matths matths 
ON matter.mmatter=matths.mhmatter) INNER JOIN son_db.dbo.cost cost ON matter.mmatter=cost.cmatter) ON periodt.pe=matths.mhper
WHERE [b]cast(cost.cbiper as datetime) BETWEEN cast(@BeginBillPer as datetime) AND cast(@EndBillPer as datetime) AND cast(periodt.pe as datetime) BETWEEN cast(@BeginPeriod as datetime) AND cast(@EndPeriod as datetime)[/b] AND matter.mjnum<>'' AND matter.mmatter NOT  LIKE '10000%'
 ORDER BY matter.morgaty, client.crelated, matter.mmatter, periodt.peendt

 

[TJRTech]

SQL will automatically convert a string parameter to a datatime parameter *IF* it is well formed, and just work without you having to do any of the conversion. Just declare the stored proc params as datetime, and tell your app developers to check the input in front-end before calling the SP (assuming you have a front-end app).

[soapbox alert]
Seems that a good 25% of the posts of late are dealing with string to datetime (or date or time) conversions in one or both directions, and it's quite sad since this stuff should be easy.

DB developers...just start always using datetime and stop storing stuff in char and varchar fields...then through datepart, datetime, casts, converts, UDFs, etc you can GET the "parts" you need.

Of course, I know "legacy code" and database departments make such rules difficult to follow.

[/soapbox alert]

TR