How get rid of time portion of dates?

[mscertified]

In a query, how do I eliminate the time portion of a date/time column so I
can group on the date?

 

[John Spencer]

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
'====================================================

 

[John W. Vinson]

In a query, how do I eliminate the time portion of a date/time column so I
can group on the date?

DateValue([datefield])


John W. Vinson [MVP]

 

[mscertified]

I used it but why does this convert 8/31/2007 to 8/1/2007 ?

 

[mscertified]

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

In a query, how do I eliminate the time portion of a date/time column so I
can group on the date?

DateValue([datefield])


John W. Vinson [MVP]

 

[John Spencer]

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
..

 

[John W. Vinson]

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

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]

 

[APath]

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

 

[Jeff Boyce]

"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

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

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]

 

[KenSheridan via AccessMonster.com]

Any repercussions will stem from the fact that the format function returns a
string, which is fine if you simply want to display a date in a given format,
and is commonly used for that.

Even though the returned value is a string it will be recognized as a date by
functions such as the DateAdd function for doing date arithmetic, but only if
the format is the regional date format in use by the system (it would not
work for me for instance in your format "m/d/yyyy" as the UK short date
format is dd/mm/yyyy) or is an internationally unambiguous format such as the
ISO standard of YYYY-MM-DD.

For date arithmetic the actual date/time value should be used. The DateValue
function returns a value of such a data type, so this can be used to strip
out the time of day from values which might include non-zero times of day,
and date arithmetic can hen be done confidently on the returned value
regardless of the regional date format in use.

One area where the Format function should be used is when building date
literals delimited by the # date delimiter character, e.g. if building a
string expression as the criterion for a function such as the DLookup
function. The value concatenated into the string expression must be in an
appropriate format. The US short date format works, but I would normally use
the ISO standard format for date notation, e.g.

Dim strCriteria As String
Dim varID as Variant

strCriteria = "MyDate = #" & _
Format(VBA.Date,"yyyy-mm-dd") & "#"

varID = DLookup("MyID", "MyTable", strCriteria)

This will work internationally, whereas if I didn't format the value the
above would fail here as, if the current date were 4 July 2009 (04/07/2009
here) and I simply used:

strCriteria = "MyDate = #" VBA.Date & "#"

this would evaluate here to "MyDate = #04/07/2009#"

but if I sent the file to one of my American friends to:

"MyDate = #07/04/2009#"

so it would work for them, but not here as the function would be looking for
7 April.

Ken Sheridan
Stafford, England

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
[quoted text clipped - 10 lines]

John W. Vinson [MVP]

 

[Sinner]

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

 

[KenSheridan via AccessMonster.com]

What's the setting for the short date format in Windows control panel? It
sounds to me like you may be using English(UK) format but entering the dates
in English(US) format. The reason dates like 10/13/2009 are OK is that
Access recognises in such cases if the day and month are transposed for the
regional format in use and switches them around.

You can find out what is the regional setting in use easily from within
Access. Just press Ctrl-G to open the debug window (aka immediate window)
and in it enter:

? Now()

and press enter. What does it then show?

BTW when formatting the value in a query you don't need to use the DateValue
function. Just Format([post date],"dd/mm/yyyy") is enough.

Ken Sheridan
Stafford, England

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

 

[Sinner]

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.

 

[KenSheridan via AccessMonster.com]

That sounds like the problem. I'd suggest that you import the dates into a
text field, not a date/time field, and then run a query to swap the month and
days around:

UPDATE [YourTable]
SET [YourDate] =
CDate(Mid(Format([YourDate],"dd/mm/yyyy hh:nn:ss"),4,2) & "/" &
Left(Format([YourDate],"dd/mm/yyyy hh:nn:ss"),2) &
Mid(Format([YourDate],"dd/mm/yyyy hh:nn:ss"),6))
WHERE Len(Nz([YourDate],"")) > 0;

Then change the data type of the field in the table from text to date/time.
You should then be able to format the date/time field normally.

Ken Sheridan
Stafford, England

 

[Sinner]

With minor modification, I have what was required.
Many thanks Ken.
God Bless.