Still problems with Stored Proc...

[fattyfatpants]

I know I posted this yesterday, but I only got 1 response and I can't figure it out...this procedure takes in 4 values for conversion from string data type to smalldatetime (mm/dd/yyyy)...I keep getting the error "Server: Msg 296, Level 16, State 3, Line 0
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
Server: Msg 296, Level 16, State 1, Line 0
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."
The two fields I am trying to convert are cost.cbiper and periodt.pe...take a look and let me know...thanks!!

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]convert(smalldatetime, cost.cbiper) BETWEEN cast(@BeginBillPer as smalldatetime) AND cast(@EndBillPer as smalldatetime) AND convert(smalldatetime, periodt.pe) BETWEEN cast(@BeginPeriod as smalldatetime) AND cast(@EndPeriod as smalldatetime)[/b]
AND matter.mjnum<>'' AND matter.mmatter NOT  LIKE '10000%' 
ORDER BY matter.morgaty, client.crelated, matter.mmatter, periodt.peendt

 

[Pattycake245]

Can you give an example of what cbiper and periodt.pe look like. And, are these two fields char, varchar.....

thanks, Tim

 

[Connatic]

Have you tried Converting the Date Values you want to use into smallDateTime, outside of the SELECT statement, so you can see if that is the problem.

Example:

If the Char Dates wont Convert using

DECLARE @TestDateConversion as smalldatetime

set TestDateConversion = cast(@EndBillPer as smalldatetime)

Then at least you can rule that in or out as the Problem !!

Other than that, your statement looks sound !

n.b - Use (brackets) to seperate out you WHERE clause, This not only tells SQL Server what to Evaluate first, but it makes your code easier to read !!

 

[TimKThailand]

This might help. Put this line after AS

...
As

SET DATEFORMAT mdy
...

 

[fattyfatpants]

Connatic, Pattycake, thanks for responding so quickly

the expected input range of cost.cbiper and periodt.pe would be 0603 (beginning) and 0404 (ending)...for some reason the database developer(s) decided to use char as opposed to smalldate...nothing seems to be working at all... when I execute the procedure in query analyzer without converting cost.cbiper and periodt.pe to smalldate no results are returned except for the field names...should I be using output parameters also??? I am stuck

(Just a little background I am trying to use this procedure from Crystal Reports 9.0 to give me my results)

 

[Pattycake245]

Forgive me if this sounds like a dumb question but if your values for cbiper are like '0404' aren't you missing a couple of numbers?

select convert(smalldatetime, '0404')

Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

whereas:

select convert(smalldatetime, '040401')

converts to

2004-04-01 00:00:00

Tim

 

[fattyfatpants]

Forgive me if this sounds like a dumb question but if your values for cbiper are like '0404' aren't you missing a couple of numbers?

Pattycake, I am explicitly assigning the day value

set @yr = right(@beginbillperiodrange, 2)
set @mn = left(@beginbillperiodrange, 2)
[b]set @dy = '01'[/b]

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

If I am setting the variables to, say, cast(@BeginBillPer as smalldatetime) will it turn the value into a date like I want it to (i.e. 06/01/2003) or will it turn it into a long date format (i.e. June 1, 2003 12:00:00 AM) because I need the date range to be tested in a format like 06/01/2003 to 04/04/2004 not in the long format...also, (I am going to try not to confuse you here) when I pass the query improperly formatted ranges
(WHERE cost.cbiper BETWEEN '0603' and '0404') through a raw query in query analyzer I get results (even though they are wrong) but when I do this same thing inside the stored procedure and try executing it nothing is displayed

 

[Connatic]

FattyFatPants,

i think your problem is to do with your table fields !

Am i right when i think you are saying the Values of cost.cbiper & periodt.pe would be in a format of

e.g. - '06030404' If this is the case then they will be giving you the conversion error !!!

 

[fattyfatpants]

Am i right when i think you are saying the Values of cost.cbiper & periodt.pe would be in a format of

e.g. - '06030404' If this is the case then they will be giving you the conversion error !!!

Actually cost.cbiper and periodt.pe are char(4) fields (in mmyy format) so they contain only single values (i.e. 0603, 0404, 0301, 0502, etc.) I have no clue as to why the developer(s) made these char and not smalldatetime or datetime. The funny thing is there is another field periodt.peendt (Period End Date) that is in datetime format, I wish I could use that but it returns wrong results...sorry to confuse you

I appreciate all your help so far everyone, thanks for being patient

 

[SQLBill]

Add this to your script before the SELECT:

SET DATEFORMAT mdy

SELECT <rest of code>


That will let SQL Server know that the dates are coming in the form of mm/dd/yyyy. You could have a date 01/24/2004 and SQL Server is misinterpreting it to be dd/mm/yyyy.

-SQLBill

 

[TimKThailand]

As I replied in my previous post, I agree with SQLBill.
Try this out.

set dateformat mdy
DECLARE @yr char(2),
@mn char(2),
@dy char(2),
@endbillperiodrange varchar(10),
@EndBillPer smalldatetime
set @endbillperiodrange='0404'
set @yr = right(@endbillperiodrange, 2)
set @mn = left(@endbillperiodrange, 2)
set @dy = '30'
set @EndBillPer = @mn + '/' + @dy + '/' + '20' + @yr
print @EndBillPer

 

[fattyfatpants]

I'm sorry for sounding like I have no idea what I'm doing but when I try TimK's solution I am expecting the printed output to be 04/30/2004 but instead it prints like Apr 30, 2004 12:00AM...is that something that can be messing up the results??

 

[TimKThailand]

You can format the result the way you want.

...
print replicate('0', 2-len(convert(varchar(2),month(@EndBillPer))))+ convert(varchar(2),month(@EndBillPer))+'/'+
replicate('0', 2-len(convert(varchar(2),day(@EndBillPer))))+ convert(varchar(2),day(@EndBillPer))+'/'+
convert(varchar(4),year(@EndBillPer))

Now it should return '04/30/2004'

 

[tektipdjango]

You have a problem with the endPeriod calculation.
If you write month/30/year what about february which has less than 30 days, and what with the months of 31 days?

Furthermore what about the cbiper field?
You need to convert it , maybe it's a string like yymmdd thet you can check like
cbiper between (@beginperiodrange + '01') and (@bendperiodrange +31) (in this case the 28 days for february is not a problem)

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​