?
Solved

Convert a Long Date to a Short Date

Posted on 2011-09-09
9
Medium Priority
?
243 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 2000 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 34

Accepted Solution

by:
Norie earned 2000 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 93

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

752 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