Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: data type conversion in stored procedure

    Hi,

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

    Code:
    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:

    Code:
     EXEC sp_mystoredproc param1, param2
    i get the error:

    Code:
    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,
    g11DB

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Post some sample data, along with the datetime values you expect them to be converted to.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2006
    Posts
    111
    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

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by 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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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
    Last edited by pootle flump; 08-03-06 at 11:28.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Actually, I just enjoy shooting people.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Aug 2006
    Location
    Bangalore
    Posts
    3
    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

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Aug 2006
    Location
    Bangalore
    Posts
    3
    I Agree, But the poster wants in MM/dd/yyyy format.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jul 2006
    Posts
    22
    Quote Originally Posted by pootle flump
    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

    Code:
    declare @d datetime
    set @d = '08/april/2006'
    select @d
    
    ---->  2006-04-08 00:00:00.000

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •