# Database Discussions > Sybase >  sybase between date query

## BJK

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&#39;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 &#39;#10/23/1997 6:55:55 AM#&#39; and &#39;#11/23/1997 6:55:55 AM#&#39; &#34;

 select * from sumstaff where txn_date between &#39;%10/23/1997 6:55:55 AM%&#39; and &#39;%11/23/1997 6:55:55 AM%&#39; &#34;

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

----------


## Joe Fung

Try the following

select * from sumstaff where txn_date between &#39;Oct 23 1997 6:55:55AM&#39; and &#39;Nov 23 1997 6:55:55AM&#39;

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&#39;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 &#39;#10/23/1997 6:55:55 AM#&#39; and &#39;#11/23/1997 6:55:55 AM#&#39; &#34;

 select * from sumstaff where txn_date between &#39;%10/23/1997 6:55:55 AM%&#39; and &#39;%11/23/1997 6:55:55 AM%&#39; &#34;

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

----------

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&#39;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 &#39;#10/23/1997 6:55:55 AM#&#39; and &#39;#11/23/1997 6:55:55 AM#&#39; &#34;

 select * from sumstaff where txn_date between &#39;%10/23/1997 6:55:55 AM%&#39; and &#39;%11/23/1997 6:55:55 AM%&#39; &#34;

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

----------


## AP

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

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

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&#39;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 &#39;#10/23/1997 6:55:55 AM#&#39; and &#39;#11/23/1997 6:55:55 AM#&#39; &#34;

 select * from sumstaff where txn_date between &#39;%10/23/1997 6:55:55 AM%&#39; and &#39;%11/23/1997 6:55:55 AM%&#39; &#34;

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

----------

