asked on

retrieving data sql 8 (datetime)

Hi experts, How to put a condition in a certain field (datetime). I have column named, theDateIn which has a data "2017-09-24 08:02:39.000", "2017-09-24 07:50:44.000". I'm trying to retrieve by using this code but it didn't work.

Select TheDescription from eCho where theDateIn Between '2017-09-24' And '2017-09-24' order by Theicnt asc

Please note that the code will work if i make '2017-09-25' or september 25, in the end part but my existing data doesn't have 25th of september all are 24th only. I also notice that it also work If i remove the time in the database. Which part of the code should need to add or change? thank you!

Kevin Cross

I would not use BETWEEN with DateTime fields because '2017-09-24' means '2017-09-24 00:00:00'; therefore, you are checking between midnight and midnight. Use >= '2017-09-24' AND < '2017-09-25' instead.

Select TheDescription 
from eCho 
where theDateIn >= '2017-09-24' And theDateIn < '2017-09-25' 
order by Theicnt asc

Open in new window

HainKurt

you can use one of these...

Select *
  from eCho
 where theDateIn Between to_date('2017-09-24','yyyy-mm-dd') And to_date('2017-09-24','yyyy-mm-dd')+1
 order by Theicnt asc

Select *
  from eCho
 where theDateIn >= to_date('2017-09-24','yyyy-mm-dd') And theDateIn < to_date('2017-09-24','yyyy-mm-dd')+1
 order by Theicnt asc

Select *
  from eCho
 where trunc(theDateIn) = to_date('2017-09-24','yyyy-mm-dd')
 order by Theicnt asc

Open in new window

* last one is not recommended if you have lots of data, say more than 10K

Kevin Cross

Agree. Try to avoid functions around column names if you can. It impacts the systems ability to use index on that column. For the first solution, the reason I avoid it is if you do have data that occurs on midnight like automated records (or in most billing systems, invoices show as invoice date with no time stamp, i.e., as of midnight); therefore, you best bet is to take the start date at midnight and add a day but use < where as between results in >= and <= and as such is a different result set potentially.

Whing Dela Cruz

ASKER

Hi HainKurt, I tried your solution but it didn't work when i run it a message says; 'to_date' is not a recognized built-in function name. I also tried Kevin Cross solution but it's not also working. I need to show all datas only for '2017-09-24', Kevin solutions need to set end part to '2017-09-25." to show all the datas on '2017-09-24'. Thanks and hope you'll give me one more solutions...

Kevin Cross

what's not working? The solution I give you will show only dates on 9/25 because I have < 9/25 at midnight. You should not get any data for 9/25.

EDIT: the point I am trying to get across is did you run the query and get an error or bad set of data OR are you assuming it will return information from 9/25 because you do not understand the solution. I can explain further if it is the latter case but will need data samples and what database platform you are on if it is the former.

SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial