How get rid of time portion of dates?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In a query, how do I eliminate the time portion of a date/time column so I
can group on the date?
 
Use DataValue function on the date field to strip the time

DateValue(SomeDateTimeField) returns just the date portion.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
I used it, but it converts 8/31/2007 to 8/1/2007 ?
I checked if I run:
datevalue(8/31/2007) I get 8/1/2007
datevalue("8/31/2007") gives 8/31/2007

John W. Vinson said:
In a query, how do I eliminate the time portion of a date/time column so I
can group on the date?
Click to expand...

DateValue([datefield])


John W. Vinson [MVP]
Click to expand...
 
I've never seen it do that. What version of Access are you using?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
I used it, but it converts 8/31/2007 to 8/1/2007 ?
I checked if I run:
datevalue(8/31/2007) I get 8/1/2007
datevalue("8/31/2007") gives 8/31/2007
Click to expand...

I have NO idea, and have never seen this happen. I could imagine
datevalue(8/31/2007) giving #12/30/1899# with some small time value, but not
giving 8/1.

What's the context? Where did you put this expression? If it's in a query,
please post the SQL; if it's on a form, please post the actual expression, and
the datatype and field value of the control that you're referencing.

John W. Vinson [MVP]
 
Not sure if this will help, I'm definitely not any type of expert and maybe
Mr. Vinson can make me aware of any repercussions in using the
following...but it works for me...

format([Datevalue],"m/d/yyyy")

for the field result starting with "8/31/2009 17:42:00" I end up with
"8/31/2009".

APath
 
"repercussions" implies use...

How can we tell you what will happen if we don't know how you intend to use
it?

Regards

Jeff Boyce
Microsoft Access MVP

APath said:
Not sure if this will help, I'm definitely not any type of expert and
maybe
Mr. Vinson can make me aware of any repercussions in using the
following...but it works for me...

format([Datevalue],"m/d/yyyy")

for the field result starting with "8/31/2009 17:42:00" I end up with
"8/31/2009".

APath


John W. Vinson said:
I have NO idea, and have never seen this happen. I could imagine
datevalue(8/31/2007) giving #12/30/1899# with some small time value, but
not
giving 8/1.

What's the context? Where did you put this expression? If it's in a
query,
please post the SQL; if it's on a form, please post the actual
expression, and
the datatype and field value of the control that you're referencing.

John W. Vinson [MVP]
Click to expand...
Click to expand...
 
I have a report with October dates like 10/01/2009 12:22:39 format and
so on.

I formated the date field in one of my queries as
Format(DateValue([post date]),"dd/mm/yyyy")

It is returning

10/01/2009 instead of 01/10/2009

for dates greater than 12, it is returning correct result like
10/13/2009 returns 13/10/2009

Any idea on how to fix this.

Using Access 2003.


Thanks
 
Hi,

I got 22/10/2009 10:09:52 PM when I typed ? now().

The settings are ok.

One thing I would like to add. The text file that I have imported is
having the date format 10/13/2009 hh:mm:yy.
Could that be the reason that access is reading it as it is.
If yes, then how can I change that to a normal dd/mm/yyyy format??

Yes datevalue wasn't necessary there : )
...was using so many options, forgot to erase that.

Hope to hear from you soon.
 
Back
Top