Solved

Convert a Long Date to a Short Date

Posted on 2011-09-09
9
234 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
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 33

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
 
LVL 33

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 33

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now