Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    13

    Unanswered: Error converting data type varchar to datetime.

    Here is the stored procedure
    the data type for the column b_trn_tentdte is decimal(8,0).

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    ALTER Procedure Prc_New_cus_wo_prev_posted
    @b_trn_tendte_Beg_Date datetime
    ,@b_trn_tendte_End_Date datetime
    @b_trn_tendte_Beg_Date1 datetime
    ,@b_trn_tendte_End_Date2 datetime

    as

    declare @Beg_tentdte datetime
    declare @End_tentdte datetime
    declare @Beg_tentdte1 datetime
    declare @Beg_tentdte2 datetime

    set @Beg_tentdte = convert(varchar(10), @b_trn_tendte_Beg_Date, 112)
    set @End_tentdte = convert(varchar(10), @b_trn_tendte_End_Date, 112)
    set @Beg_tentdte1 = convert(varchar(10), @b_trn_tendte_Beg_Date1, 112)
    set @Beg_tentdte2 = convert(varchar(10), @b_trn_tendte_End_Date2, 112)


    /* this query looks for customers with posted
    transactions but none prior to the date range
    in question */

    Select distinct b_cus_cname
    from
    bar_cus_db_rec
    ,bar_trn_db_rec b
    where b.b_trn_instid = ''
    and b.b_trn_instid = b_cus_instid
    and b.b_trn_actid = b_cus_cusid
    and convert(varchar(10), b.b_trn_tentdte) between @Beg_tentdte and @End_tentdte
    and not exists (select c.b_trn_actid
    from bar_trn_db_rec c
    where c.b_trn_instid = b.b_trn_instid
    --and c.b_trn_tentdte between 19970901 and 20030229
    and convert(varchar(10), c.b_trn_tentdte) between @Beg_tentdte1 and @Beg_tentdte2
    and c.b_trn_actid = b.b_trn_actid)

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    every time I run the query, I get this error message:
    Error converting data type varchar to datetime.

    thanks for your assistance.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am wondering if you are doing any data validation on your paramaters in you application before you call your sp.

    If isDate(parameter) Then
    'call your sp
    Else
    'prompt user for input
    End if
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    Disregard previous.

    Disregard previous.

    you are coverting your parameters to char and trying to assign them to a dattime variable.

    set @Beg_tentdte = convert(varchar(10), @b_trn_tendte_Beg_Date, 112)
    set @End_tentdte = convert(varchar(10), @b_trn_tendte_End_Date, 112)
    set @Beg_tentdte1 = convert(varchar(10), @b_trn_tendte_Beg_Date1, 112)
    set @Beg_tentdte2 = convert(varchar(10), @b_trn_tendte_End_Date2, 112)

    remove your converts
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SQL Server will implicitly convert between character strings and datetime values if the strings are in the proper format. But in your case you are starting with a numeric value.
    A numeric value such as 19970901 cannot be implicitly converted to a datetime datatype, even after casting as a character string. You will need to explicitly convert to datetime, like this:
    SELECT CONVERT(datetime, CONVERT(varchar(10), c.b_trn_tentdte))

    ...and for good measure you should probably be more specific about the format, like this:
    SELECT CONVERT(datetime, CONVERT(varchar(10), c.b_trn_tentdte), 112)
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...and if you stick around long enough, you "may" become a "Format Man"!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    sir! cannonball over the fore end sir.

Posting Permissions

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