data type conversion in stored procedure

Hi,

I have a stored procedure a portion of which looks like this:

IF (CAST (@.intMyDate AS datetime)) IN (
SELECT DISTINCT SHOW_END_DATE
FROM PayPerView PP
WHERE PP_Pay_Indicator = 'N' )
BEGIN
--ToDo Here
END

where:
@.intMyDate is of type int and is of the form 19991013
SHOW_END_DATE is of type datetime and is of the form 13/10/1999

however when I run the procedure in sql query analyzer as:

EXEC sp_mystoredproc param1, param2

i get the error:

Server: Msg 242, Level 16, State 3, Procedure usp_Summary_Incap, Line 106
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

what is the proper way of doing the conversion from int to datetime in stored procedure?

thank you!

cheers,
g11DBPost some sample data, along with the datetime values you expect them to be converted to.|||Hi blindman,

Below are the sample data:

@.intMyDate is of type int and is of the form 19991013
SHOW_END_DATE is of type datetime and is of the form 13/10/1999

I just want to convert @.intMyDate of type int which is of the form 19991013 to a type datetime which is of the form 13/10/1999

I'm thinking of converting int to varchar and then concatenating the three parts and adding "/" such that "13/10/1999".
Then using the function Convert

Convert(datetime, "13/10/1999", 103)

Is this the best way to go about it?

Thank you again.

cheers,
g11DB|||I'm thinking of converting int to varchar and then concatenating the three parts and adding "/" such that "13/10/1999".
Then using the function Convert

Convert(datetime, "13/10/1999", 103)

Is this the best way to go about it?
Yes - except convert it to ISO format:
YYYY-MM-DD
Get into that habit and no matter where you find yourself working you never need to worry if SQL Server thinks 08/04/2006 is the 8th of April or the 4th of August.

HTH|||Yes, convert it to char(8) and then deal with it as a string.
And when you find some spare time, go and shoot the idiot who decided to store date values that way. You'll be glad you did.|||In case you are interested (and even if you are not :)) casting 19991013 as a date was basically saying to SQL Server:
Please add 19, 991, 013 days to 1st Jan 1900 and give me the date. SQL Server obviously isn't future proofed because it can't handle dates 54 thousand years into the future.

That's why Blindman shoots people like that :)

probably the most exciting thing you'll read about SQL Server dates this week:
http://www.dbforums.com/showthread.php?t=1212546|||Actually, I just enjoy shooting people.|||Hi,
try this
DECLARE @.intMyDate INT
DECLARE @.FFDATE VARCHAR(50)
DECLARE @.TDATE DATETIME
SET @.intMyDate = 19991013

SET @.FFDATE = SUBSTRING(CAST(@.intMyDate AS VARCHAR(8)),5,2) + '/' + SUBSTRING(CAST(@.intMyDate AS VARCHAR(8)),7,2) + '/' + SUBSTRING(CAST(@.intMyDate AS VARCHAR(8)),1,4)
SET @.TDATE = CAST(@.FFDATE AS DATETIME)

PRINT @.TDATE|||That's jolly good code however I would still recommend you use the same technique but output the date in ISO (YYYY-MM-DD) format. If you don't believe me then check the link I provided and you'll see Pat making the same point :)|||I Agree, But the poster wants in MM/dd/yyyy format.|||No, he just wants to convert it to a datetime datatype, so the preference for formatting the intermediary string as YYYY-MM-DD is valid. How the resulting datetime value is displayed is a secondary issue. Please revue the section on datetime datatype in Books Online.|||Yes - except convert it to ISO format:
YYYY-MM-DD
Get into that habit and no matter where you find yourself working you never need to worry if SQL Server thinks 08/04/2006 is the 8th of April or the 4th of August.

HTH

and you always have the chance of use words for months anyway, so '08/april/2006' never would be 4th of august ;)

declare @.d datetime
set @.d = '08/april/2006'
select @.d

--> 2006-04-08 00:00:00.000