Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    31

    Unanswered: Doing airithmetic on date/time ???

    I have a column, c1 which is of datatype datetime and the column values a stored like this m/d/yyyy h:mm AM(or PM)

    EXAMPLE
    12/10/2005 2:00 AM and 1/4/2006 11:00 PM

    How can do a query that between those dates ?

    Also I'm somewhat familiar with the DATEADD function but is there a function (like a DATESUBTRACT) to do exactly the opposite?


    I'm using Sybase ASE 12.5.2
    Last edited by bluwulf; 01-07-06 at 18:34.

  2. #2
    Join Date
    Dec 2005
    Posts
    39
    To query between dates you can try this

    select @fromdate = convert(datetime, convert(varchar(10), getdate(), 101))
    select @todate = convert(datetime, convert(varchar(10), getdate(), 101))

    select ... from ... where <datefield> >= @fromdate and <datefield> < @todate

    I've not given the entire code, just some skeleton ones, so you may want to make necessary changes. Pls change getdate() in the initial 2 selects to appropriate date you want. also note that that they would be defaulted to 12:00AM which could result in more data being returned.

    On the other hand if you use

    select ... from ... where <datefield> >= getdate() and <datefield> < dateadd(day, 1, getdate())

    result set could be different because this will result in actual time on the server correct to microseconds & if that's how data is stored in dbase result set could fluctuate. You can also use "between" clause instead of >= & <.

    There's no datesubtract function, however you can use negative numbers in dateadd clause which will result in date being subtracted, something like

    select dateadd(day, -n, getdate())

  3. #3
    Join Date
    Oct 2004
    Posts
    31
    Thanx very much

  4. #4
    Join Date
    Oct 2004
    Posts
    31
    One minor "problem" though I notice that the <= operator doesn't work as I would expect it to:



    Code:
    DECLARE @fromdate DATETIME
    SELECT @fromdate=CONVERT(DATETIME,'12/17/2005',101)
    
    DECLARE @todate DATETIME
    SELECT @todate=CONVERT(DATETIME,'01/[b]07[b]/2006',101)
    
    
    select ... from ... where <datefield> >= @fromdate and <datefield> <= @todate

    The above query only returns data up to 01/06/2006, Why didn't it include data up to the 01/07/2006?

    The => operator included (as expected) data from 12/17/2005 onwards


    Probably I would have to refine the date ranges (as you indicated in your post):
    Last edited by bluwulf; 01-07-06 at 23:51.

  5. #5
    Join Date
    Dec 2005
    Posts
    39
    when you're using >= < always have end date 1 day more than what you expect to see. so if you want to see 01/07/06 have todate as 01/08/06 and < will ensure that 07 is included as well. this is better than using >= & <=. Alternatively you can try with between clause as well, however in this case todate need not be incremented by 1.

    select ... from ... where datefield between "12/17/2005" and "01/07/2006"

    check which approach gives the desired output w/o any compromise in performance.

  6. #6
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    First, remember that datetime values are stored in a native format, and include milliseconds, so when you compare a datetime value with a date string you have to take this into account.

    You can also use the BETWEEN operator for your query:

    select ... from ... where the_date between @date1 and @date2

    Michael

Posting Permissions

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