Solved

Convert a Long Date to a Short Date

Posted on 2011-09-09
9
241 Views
Last Modified: 2012-06-21
I have a columm in my table set as a "short date"

it is displayed as 23/08/2011

but when I click in to the table it comes out as 23/08/2011 09:18:00.

can I use a function to convert it to  23/08/2011

I ma trying to use it in a group query but keep geeting the wrong answer as all the times are shown

0
Comment
Question by:Brogrim
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 34

Assisted Solution

by:Norie
Norie earned 500 total points
ID: 36510941
What's the problem with the grouping and what are you trying to group by?

What database are you using?

There are various ways to deal with this but it does kind of depend on that.

0
 

Author Comment

by:Brogrim
ID: 36510958
I ma using ms access 2003

Whn i group them it is grouping the time in the date so instead of getting 20 results for a date I am getting 5 because it is grouping the time
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36511013
Format([YourDateField],"dd/mm/yyyy")
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 34

Accepted Solution

by:
Norie earned 500 total points
ID: 36511022
If it's just the time you want to remove you can use INT.

JustDate:INT([DateTimeField])

You can then group by that.
0
 

Author Comment

by:Brogrim
ID: 36511107
Format([YourDateField],"dd/mm/yyyy")

the above is not working, getting incorrect results

JustDate:INT([DateTimeField])

is returning numbers

I want to make a new table with the value only holdig a short date
0
 

Author Comment

by:Brogrim
ID: 36511198
found the solution

UPDATE TableName SET TableName.DateTimeField =
DateValue([TableName].[DateTimeField]);
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36511199
Try:

DateValue([YourDate])
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36511204
>>I have a columm in my table set as a "short date"

That is a DISPLAY format, and has no bearing on how Access stores the value.

dqmq's suggestion of Format should work; please explain what you mean by "getting incorrect results".

In any event, Format returns a string.  If you need to return a true date value:

JustDate: CDate(Int(DateTimeField))
0
 
LVL 34

Expert Comment

by:Norie
ID: 36511254
Those numbers are dates, they might just need formatting as dates, but don't use Format unless you want to convert them to text.

You can format fields in a query by right clicking, selecting Properties and setting the Format property appropriately.

What exactly did you try anyway?

When I try it with a date/time field I get dates.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

688 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