Post reply

Re: comparing dates

  • June 29, 2005 at 9:53 am

    #186434

    Hi all,

    I'm trying to get the date from the table and compare w/ today's w/o the time.

    I received this error from MS Access:

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    Could someone take a look at this sproc below-

    CREATE PROCEDURE spCheck_shipping_rpt_date

     @opt integer

    as

    declare @curr_date datetime, @sys_date datetime

    if @opt = 0

    Begin

    select  @sys_date = CAST(tblShipping_rpt_date.shippg_rpt_dt AS varchar(10)) from  tblShipping_rpt_date

    set @curr_date = cast(getdate() as varchar(10))

    if  @sys_date <> @curr_date

    Begin

     UPDATE tblshipping_rpt_date SET shippg_rpt_dt = getdate(), status = 0;

     return(-1)

    End

    Else

     return(0);

    End

    Thanks for your assistance

  • June 29, 2005 at 10:01 am

    #569394

    I assume that either of these line is causing you the problem.

    Can you run the code from QA and see which lines causes the error?

    select @sys_date = CAST(tblShipping_rpt_date.shippg_rpt_dt AS varchar(10)) from tblShipping_rpt_date

    set @curr_date = cast(getdate() as varchar(10))

  • June 29, 2005 at 10:03 am

    #569397

    btw this works for me, so I assume that the first line is erroring

    Declare @a as datetime

    Select @a = cast(getdate() as varchar(10))

    Select @a

    try

    Select * from dbo.tblShipping_rpt_date where isdate(shippg_rpt_dt) = 0

    this will tell you which row of data has an invalid date.

  • June 29, 2005 at 10:07 am

    #569398

    Here's two very helpful articles on working with dates in sql server:

    http://www.karaszi.com/SQLServer/info_datetime.asp

    http://www.sql-server-performance.com/fk_datetime.asp

  • June 30, 2005 at 9:16 am

    #569885

    The error message (conversion of a char data type to a datetime data type) indicates that there is a problem with trying to load the converted datetime to the variable you have declared.

    You declared your variables as datetime.  It may be as simple as declaring these as varchars.

  • June 30, 2005 at 9:18 am

    #569887

    Or actually using the correct datatype for the column in the first place and avoiding this problem altogether.

  • June 30, 2005 at 10:34 am

    #569974

    That was exactly my problem. Thanks! Bellis. 

    And you're right about using the correct datatype.  Thanks! Remi.

  • June 30, 2005 at 10:35 am

    #569975

    NP.

  • June 30, 2005 at 10:45 am

    #569987

    Wow -- I got it right.  Maybe we should add this to the stupid Question of the Day.  Maybe then I would get one right for a change.

    Glad to be helpful to you.

    Enjoy!

  • June 30, 2005 at 11:22 am

    #570011

    Quoting myself :

    Declare @a as datetime

    Select @a = cast(getdate() as varchar(10))

    Select @a

    try

    Select * from dbo.tblShipping_rpt_date where isdate(shippg_rpt_dt) = 0

    this will tell you which row of data has an invalid date.

    Maybe I'm just not using plain english these days.

  • June 30, 2005 at 11:49 am

    #570033

    Is that "plain" as in boring, or "plain" as in understandable? 

    I wasn't born stupid - I had to study.

  • June 30, 2005 at 11:52 am

    #570034

    Both . Especially the third one.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply