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
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
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
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') AND
convert(datetime, 'May 21 2002 9:16:00:000PM')
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