Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2008
    Posts
    13

    Unanswered: Why does this select generate an error

    Hi everyone!

    Why does the following select work...

    Code:
    select
    	count(distinct l.PID)
    from
    	LocTrackHist l, Serv s
    where
    	l.SID = s.SID and
    	l.PID = s.PID and
    	l.currFacID = 'RVH' and
    	l.currLocID like 'M08%' and
    	datediff(hh, s.startDtm, getdate()) < 24
    but this select fails with the error being "Arithmetic overflow during implicit conversion of VARCHAR value '21/06/2010 3:46:58.116 PM' to a DATETIME field ."

    Code:
    select
    	count(distinct l.PID)
    from
    	LocTrackHist l, Serv s
    where
    	l.SID = s.SID and
    	l.PID = s.PID and
    	l.currFacID = 'RVH' and
    	l.currLocID like 'M08%' and
    	datediff(hh, s.startDtm, '21/06/2010 3:46:58.116 PM') < 24

  2. #2
    Join Date
    May 2010
    Location
    Hyderabad, India
    Posts
    16
    USE datediff(hh, s.startDtm, '06/21/2010 3:46PM) < 24

    I think it will work fine.

    First convert both dates to same format.
    Last edited by reddy_546; 06-24-10 at 04:02.
    -PavanKumar M Reddy

  3. #3
    Join Date
    Jun 2010
    Posts
    51
    This could be because of date format. Sybase may be using MM/DD/YYYY format but you are passing date in DD/MM/YYYY format. Since 21 can't be converted to valid month, error is thrown by sybase. Use the solution mentioned by Reddy_546. That should work fine.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I also suggest you don't use functions on columns in your where clause
    using the datediff function on startDtm means that you won't be able to use an index defined on the startDtm column
    instead I suggest
    Code:
    where startDtm>dateadd(hh,-24,convert(datetime,'21/06/2010 3:46:58.116 PM',103))

  5. #5
    Join Date
    Sep 2008
    Posts
    13
    Thanks for all the suggestions folks!

Posting Permissions

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