[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 463
  • Last Modified:

Filtering by date

This is probably something simple, just want a second look.  I have a db with a field "Datetobecompleted".  I am trying to run a simple query that will display results for today and today +3.  I have put todays date and went up to the 14th and still no luck.  The query I am using is below.  I want to add that I use this query on another db and it works just fine.  I do not understand why it isnt working on this.  Any help would be more than greatly appreciated.

Query:
SELECT tbljobstodo.[DateToBeCompleted]
FROM tbljobstodo
WHERE (((tbljobstodo.[DateToBeCompleted]) Between Date() And Date()+3));
0
russell12
Asked:
russell12
1 Solution
 
lwadwellCommented:
first guess ... check the datatype on the column definition.
0
 
russell12Author Commented:
the datatype is set to date/time.
0
 
lwadwellCommented:
and there definitely rows in the table in that date range?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
russell12Author Commented:
yes.  I have only 1 row with that date.  I am going to copy and paste the date thats in that field below.

5/14/2012

I am so confused about this cause it should work.  I can no believe that this is creating such an issue!!  Thanks though for quick response.   I am sure its something very simple.
0
 
rajshekherCommented:
Hi,

First thing is that your data type of the field should be Date/Time format.

If it is, then you can apply you query.

SELECT tbljobstodo.[DateToBeCompleted]
FROM tbljobstodo
WHERE tbljobstodo.[DateToBeCompleted] Between  GETDATE() and GETDATE() + 6

Hope it will resolve your issue. ( GetDate() is MS SQL Format )
0
 
lwadwellCommented:
Does +4 work?  It might be a boundary condition.
Is there a time portion in the date?
0
 
russell12Author Commented:
what do u mean by time portion?  And to rajshekher, The getdate throws a function error.  I am using ms access 2003 with sql server backend.
0
 
lwadwellCommented:
The field is datetime ... i.e. it could contain a time component as well, e.g. 5/14/2012 05:00:00
0
 
russell12Author Commented:
Oh and the +4 doesnt work either.  This is making no sense to me, I have a db that uses this same filter function Between Date() And Date()+3 and it works flawlessly.
0
 
russell12Author Commented:
Oh ok, I thought thats what u were meaning just wasnt sure.  No the only thing in that field is 5/12/2012.
0
 
lwadwellCommented:
humour me and try :

SELECT tbljobstodo.[DateToBeCompleted], format(tbljobstodo.[DateToBeCompleted], "dd mmm yyyy")
FROM tbljobstodo

Open in new window


no WHERE clause
0
 
russell12Author Commented:
Date To Be Completed      Expr1
2012-05-14 00:00:00.000      2012-05-14 00:00:00.000

thats what the outcome is.
0
 
russell12Author Commented:
If I put #5/14/2012# in the where clause, it works.  Its just something about Date()
0
 
lwadwellCommented:
Try this to see what is returned from the functions:

SELECT tbljobstodo.[DateToBeCompleted], Date(), Date() + 3
FROM tbljobstodo
0
 
russell12Author Commented:
Ok I found the issue.  It is a problem with storing date/time in sql.  The last query you had me run made me find the issue.  Sql server stores the date/time as 2012-05-14 00:00:00.000.  The Date() shows 8/14/2012.  I thought the issue was i put 05/14/2012 but it wasnt.  So i have to have the db put the information in as 08/14/2012.  It shows on the textbox as 8/14/2012.  Thanks for pointing me in the right direction.  I am going to give you the 500 points for this, but i am going to keep it open until i get it fixed.
0
 
lwadwellCommented:
Wait ... month 5 is May, 8 is August ... I was looking straight past that.
0
 
russell12Author Commented:
Ok finally it works.  I knew it was something simple.  Thank you so much for your help and walking through this with me.  I was working on this simple crap for 4 hrs!!! Thanks again!!

The solution was the way it was storing in the sql db.
0
 
lwadwellCommented:
It is always the obvious that gets you ... me ... everybody.
0
 
als315Commented:
Upload your table with some sample strings. Your syntax seems correct.
DBdate.accdb
0
 
russell12Author Commented:
Thanks for helping me get through this!!
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now