Results 1 to 4 of 4

Thread: sybase between date query

  1. #1
    BJK Guest

    sybase between date query

    Here is my problem I have a database that I am trying to query to find dates that are between a range. I am querying a field that has a datetime datatype. I can't figure out the right syntax that will produce records. The field I am querying is in the following format:

    10/23/1997 6:55:55 AM

    Here are some of the queries I have tried

    select * from sumstaff where txn_date between '#10/23/1997 6:55:55 AM#' and '#11/23/1997 6:55:55 AM#' "

    select * from sumstaff where txn_date between '%10/23/1997 6:55:55 AM%' and '%11/23/1997 6:55:55 AM%' "

    The above queries don't produce any errors but they don't produce any records either. I have looked at the table and the data is there. Any help would be greatly appreciated. Thanks... BJK

  2. #2
    Joe Fung Guest

    sybase between date query (reply)


    Try the following

    select * from sumstaff where txn_date between 'Oct 23 1997 6:55:55AM' and 'Nov 23 1997 6:55:55AM'

    cheers
    Joe


    ------------
    BJK at 5/2/2002 8:43:16 AM

    Here is my problem I have a database that I am trying to query to find dates that are between a range. I am querying a field that has a datetime datatype. I can't figure out the right syntax that will produce records. The field I am querying is in the following format:

    10/23/1997 6:55:55 AM

    Here are some of the queries I have tried

    select * from sumstaff where txn_date between '#10/23/1997 6:55:55 AM#' and '#11/23/1997 6:55:55 AM#' "

    select * from sumstaff where txn_date between '%10/23/1997 6:55:55 AM%' and '%11/23/1997 6:55:55 AM%' "

    The above queries don't produce any errors but they don't produce any records either. I have looked at the table and the data is there. Any help would be greatly appreciated. Thanks... BJK

  3. #3
    Guest

    sybase between date query (reply)

    You could try removing the # and % signs why are you using them ?


    ------------
    BJK at 5/2/2002 8:43:16 AM

    Here is my problem I have a database that I am trying to query to find dates that are between a range. I am querying a field that has a datetime datatype. I can't figure out the right syntax that will produce records. The field I am querying is in the following format:

    10/23/1997 6:55:55 AM

    Here are some of the queries I have tried

    select * from sumstaff where txn_date between '#10/23/1997 6:55:55 AM#' and '#11/23/1997 6:55:55 AM#' "

    select * from sumstaff where txn_date between '%10/23/1997 6:55:55 AM%' and '%11/23/1997 6:55:55 AM%' "

    The above queries don't produce any errors but they don't produce any records either. I have looked at the table and the data is there. Any help would be greatly appreciated. Thanks... BJK

  4. #4
    AP Guest

    sybase between date query (reply)

    The problem is potentially due to date conversion problems. Try this one:

    select * from sumstaff WHERE txn_date BETWEEN
    convert(datetime, 'May 20 2002 9:26:00:000PM&#39 AND
    convert(datetime, 'May 21 2002 9:16:00:000PM&#39

    As a last note, I know you could potentially have date precision problems - at least the datediff function does when trying to calculate milliseconds. Refer to Sybase reference manual for a more detailed explanation.

    I hope this helps,
    AP

    ------------
    BJK at 5/2/2002 8:43:16 AM

    Here is my problem I have a database that I am trying to query to find dates that are between a range. I am querying a field that has a datetime datatype. I can't figure out the right syntax that will produce records. The field I am querying is in the following format:

    10/23/1997 6:55:55 AM

    Here are some of the queries I have tried

    select * from sumstaff where txn_date between '#10/23/1997 6:55:55 AM#' and '#11/23/1997 6:55:55 AM#' "

    select * from sumstaff where txn_date between '%10/23/1997 6:55:55 AM%' and '%11/23/1997 6:55:55 AM%' "

    The above queries don't produce any errors but they don't produce any records either. I have looked at the table and the data is there. Any help would be greatly appreciated. Thanks... BJK

Posting Permissions

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