Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74

    Unanswered: What is wrong with the code in the stored procedure “sp_FetchBillets”

    The error is:
    erver: Msg 242, Level 16, State 3, Procedure sp_FetchBillets, Line 42
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    The error is on line:
    Select @DateTimeStamp_Compare = CONVERT(DATETIME,@DateTimeStamp_Interim)

    But I reaqad somewhere that:

    In your queries and other SQL statements, always represent date in yyyy/mm/dd format. This format will always
    be interpreted correctly, no matter what the default date format on the SQL Server is. This also prevents the
    following error, while working with dates:

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

    Below is my code:

    ##############################################/

    --exec sp_FetchBillets
    CREATE procedure sp_FetchBillets as
    PRINT 'Code Goes Here – Something is triggered'
    DECLARE @BilletID_Inserted INT,
    @BilletName_Inserted VARCHAR(40),
    @Description_Inserted VARCHAR(255),
    @DateTimeStamp_Inserted VARCHAR(30),
    @UpdaterID_Inserted INT

    DECLARE @DateTimeStamp_Temp VARCHAR(30), @DateTimeStamp_Compare DATETIME
    DECLARE @DateTimeStamp_Interim VARCHAR(30)

    --TIME datatype DATETIME datatype

    /*
    Do this once; get todays date in the form:7/22/2002
    select getdate(), convert(char(24), getdate(),101), convert(char(24),getdate(),112)
    */

    -- get the leading zero off the date from getdate()
    --EXEC @monthNumAsString = sp_MonthToNumberString @monthName

    Select @DateTimeStamp_Temp = convert(char(24), getdate(),101)
    EXEC @DateTimeStamp_Interim = sp_StripDateTime @DateTimeStamp_Temp


    /*
    In your queries and other SQL statements, always represent date in yyyy/mm/dd format. This format will always
    be interpreted correctly, no matter what the default date format on the SQL Server is. This also prevents the
    following error, while working with dates:

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

    CONVERT(char(20), ytd_sales)
    */

    /* The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.*/
    --Select @DateTimeStamp_Compare = CAST(@DateTimeStamp_Interim AS DATETIME) -- BOMBS

    /* The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.*/
    Select @DateTimeStamp_Compare = CONVERT(DATETIME,@DateTimeStamp_Interim) -- Server: Msg 242, Level 16, State 3


    SELECT @BilletID_Inserted = [BilletID] FROM NGBCST_7032002.dbo.Billet where [DateTimeStamp] = @DateTimeStamp_Compare
    SELECT @BilletName_Inserted = [BilletName] FROM NGBCST_7032002.dbo.Billet where [DateTimeStamp] = @DateTimeStamp_Compare
    SELECT @Description_Inserted = [Description] FROM NGBCST_7032002.dbo.Billet where [DateTimeStamp] = @DateTimeStamp_Compare
    SELECT @DateTimeStamp_Inserted = [DateTimeStamp] FROM NGBCST_7032002.dbo.Billet where [DateTimeStamp] = @DateTimeStamp_Compare
    SELECT @UpdaterID_Inserted = [UpdaterID] FROM NGBCST_7032002.dbo.Billet where [DateTimeStamp] = @DateTimeStamp_Compare

    /*Select * from dbo.*/

    INSERT NGBCST_7032002.dbo.Billet_Mart (BilletID, BilletName, Description,DateTimeStamp,UpdaterID) VALUES (@BilletID_Inserted,
    @BilletName_Inserted,
    @Description_Inserted,
    @DateTimeStamp_Inserted,
    @UpdaterID_Inserted)

    ##############################################/

    * strip the date got by getdate() of a leading zero*/
    CREATE PROCEDURE sp_StripDateTime
    (
    @DateTimeStamp_Temp VARCHAR(30)
    )
    AS
    DECLARE @DateTimeStamp_Return VARCHAR(30) , @strLen INT, @intZeroPos int


    /* check to see whether the first entry in say,
    "07/22/2002" is a zero*/
    --if @DateTimeStamp_Temp.substr(4,1) != '0' BOMBS
    --Select @boolZero = @DateTimeStamp_Temp.substring("0", 4, 1 ) BOMBS
    --Select @boolZero = substring("0", 4, 1 ) BOMBS with

    --select @intZeroPos = InStr(@DateTimeStamp_Temp, "0")
    IF SUBSTRING (@DateTimeStamp_Temp, LEN (@DateTimeStamp_Temp), 1) = '0'
    --if @boolZero = true


    /* do nothing and just return*/
    RETURN @DateTimeStamp_Temp

    else
    --BEGIN (introds an error in the next execable line
    /* proceed to process the parameter*/
    Select @strLen = Len(@DateTimeStamp_Temp)

    -- Check to see whether the string's last char is =
    -- If so, trim it off
    Set @DateTimeStamp_Temp = Right(@DateTimeStamp_Temp, @strLen - 1)
    Set @DateTimeStamp_Return = @DateTimeStamp_Temp

    Set @DateTimeStamp_Return = @DateTimeStamp_Temp

    RETURN @DateTimeStamp_Return

    --END

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Whoaaaa Nelly !!!! Your sp_StripDateTime is the problem - your logic is flipped. You are telling it to strip the last character if the first character is not 0. What are you trying to accomplish with sp_StripDateTime ? Why do you need to convert the getDate into a varchar ?

  3. #3
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74
    Thanks for your response.

    I solved a lot of my problems witha simple function "SUBSTRING" as in

    SELECT @MMString = SUBSTRING(@DateTimeStamp_Temp, 2, 1)

    I was using the fuction to get me a clean say "7/26/2002" instead of "07/26/200" and the eventually break the date into "7", "26", and "2002".

    I do not need that function anymore.

  4. #4
    Join Date
    Jul 2002
    Posts
    2
    To break your date into day, month and year ... you can use

    DATENAME(datepart , date)

    Example

    SELECT DATENAME(month, getdate()) AS 'Month Name'

    which will return the month...with correct datepart paarmeter you could get day, month and year ...why to re - invent the wheel again ?

    Saerch for "DATENAME" in SQL Server Books Online

    Thanks

    Charles Selvaraj

Posting Permissions

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