• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 983
  • Last Modified:

Access Date Only In A Date/Time Variable

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
Nolanc
Asked:
Nolanc
  • 4
  • 3
1 Solution
 
Gustav BrockCIOCommented:
You can use native SQL:

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

/gustav
0
 
NolancAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
NolancAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
NolancAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
Jacques Bourgeois (James Burger)PresidentCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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