?
Solved

Access Date Only In A Date/Time Variable

Posted on 2012-08-18
8
Medium Priority
?
964 Views
Last Modified: 2012-08-18
Hi

I have an Access 2007 Database and I am accessing a  table which has an entity (among other entities) named LogDate which is of Date/Time data type. Using SQL, I need to access these log records within a selected date range. At the moment I am doing the comparison as follows:

Select * From LogTable Where CTOD(Mid(LogDate,1,10)) Between SelStart And SelEnd.

I am getting the desired result but I just seem to think there must be a better way of doing this.

I am using VB.Net of Visual Studio 2010 and running under Windows 7.

Is there a better way ?.

Many thanks.
0
Comment
Question by:Nolanc
  • 4
  • 3
8 Comments
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 38307376
You can use native SQL:

Select * From LogTable Where Int(LogDate) Between SelStart And SelEnd

/gustav
0
 

Author Comment

by:Nolanc
ID: 38307416
Hi cactus_data

I must confess I don't understand what you are suggesting. Surely Int(LogDate)  implies you are extracting the Integer portion of LogDate. Int is applicable to Numeric data only isn't it.

Thanks
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 38307577
Yes, but the underlying data type of Date is Double.
A decimal of such a value is the time part, thus stripping this leaves the date part. Further, Int is "neutral" and doesn't change the data type.

To be strict, Fix should be used in favour of Int but that only matters for dates before 1899-12-30.

CTOD(Mid(LogDate,1,10)) casts logdate to a string, pulls 10 chars from this, and then converts it back to a date (using a FoxPro function?).

/gustav
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:Nolanc
ID: 38307616
Hi

I'm sorry, I use the function CDate and not CTOD. If I use  CDate(Mid(LogDate,1,10)) or alternatively DateValue(LogDate) I get the same result - which is the correct result. When I use Int(LogDate) , SQL tells me Logdate is not a numeric field.

What is your view on using the DateValue function instead.

Thanks.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 38307629
Then LogDate is not of data type Date but Text.

If so, use:

Select * From LogTable Where Int(CDate(LogDate)) Between SelStart And SelEnd

or the direct method:

Select * From LogTable Where DateValue(LogDate) Between SelStart And SelEnd

/gustav
0
 

Author Comment

by:Nolanc
ID: 38307726
Hi

LogDate is defined as Date/Time. Examples of data in LogDate are the following:

12/05/2011 11:19:29
27/05/2011 13:42:24

In my application I populate this field with Now. I will therefore persist with your proposed statement viz:

Select * From LogTable Where Int(CDate(LogDate)) Between SelStart And SelEnd  (A)

since it is giving me the desired result.

Is this your solution ?. If yes, I will close the question and award the points.

Thanks
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 1200 total points
ID: 38307753
Well, OK, but it doesn't make sense. CDate returns a Date/time value which Int can read and remove the timepart from. However, if LogDate is of Date/time, Int can read that LogDate directly.
Cdate(LogDate) just reads the numeric (Double) value of LogDate and converts that to a Date/time value causing no change in neither data type nor value.

/gustav
0
 
LVL 40
ID: 38308201
Did you really need to use Now() in your application to define the date. If you do not need the time portion, you should use Date() instead of Now(). Date() always records a time of 00:00:00, so you do not have to take care of the time in operations where it is not pertinent.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question