Solved

Convert a Long Date to a Short Date

Posted on 2011-09-09
9
237 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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
 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

770 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