473,405 Members | 2,310 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,405 software developers and data experts.

Is there anyway to change numbers to a date format?

Im rebuilding a database and need to covert a field where the value is a date but its in as '20060603'

I'd like to make a query to convert it to
'03/06/2006'

Is this possible, if so, how?

Also, I have to do it for 14 fields so is there an easy way to do them all as one query?

Thanks
Jul 25 '14 #1
1 794
twinnyfo
3,653 Expert Mod 2GB
If your "Date" field is always exactly as you display it, in text format, try this:

Expand|Select|Wrap|Line Numbers
  1. CDate(Left(DateField,4) & "-" & _
  2.     Mid(DateField,5,2) & "-" & _
  3.     Right(DateField,2))
This should disambiguate the date field into text that "looks"" like a date to Access. It should put it in the right format, but it may also be affected by your regional settings on your system.

You may have to trick the code more by converting the month number to a text number (i.e. "06" becomes "Jun"), but try this first.
Jul 25 '14 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Miguel Orrego | last post by:
Hi, My asp pages that display data from databases have suddenly changed the date format they display from UK to US, i.e. 11/17/2001 for 17th Nov 2003. I have checked on the win2k sp3 server...
0
by: Karam Chand | last post by:
Hello Is there anyway to change the default date format (possibly in a .ini or .cfg file) from YYYY-MM-DD to another format ? (ie, mm/dd/yyyy or any user-defined format). The useage that I'm...
1
by: Gani Pola | last post by:
Can someone please tell me how I can change the date format (e.g dd/mm/yyyy, mm/dd/yyyy) in a configuration file? In my windows app, I can retrieve the windows setting but not my ASP.NET...
4
by: Claudia Fong | last post by:
Hi Is there a way to change a date format dd/mm/yyyy to m/d/yy? I have a textBox where the user should put a date, but no matter what format the user input dd/mm/yyyy or m/d/yy, I need to...
1
by: vrparekh | last post by:
in my webapplication,user pick date in dd/mm/yy format, and i want to compare that date with another date using compare validator but compare validator compare the dates using mm/dd/yy format. ...
8
by: yashiro | last post by:
Hello i Have a database in SQLserver. I am using php to connect to it and to retrieve data from that database. I have a table with a column (DATATIME) where i do : $result =...
1
by: Chetana | last post by:
hi i need help, i want to chang the date of iis server i want to set date in format dd/mm/yyyy so that my application vartual derictory uses this date format . thanks in advance
2
by: tariquetuku | last post by:
Can anyone plz advice, how to change mysql date format to (d-m-Y) permanently which will affect all the databases in the mysql server. i.e, i want to change the date format in one place which will...
0
by: samgov | last post by:
Is it possible to change the date format in MS Projects to display as DD/MM/YY? The format that I am looking for is not available under the "Date Format" options tab.
2
by: melvintcs | last post by:
SELECT receivedDate FROM purchase WHERE receivedDate Between #1/2/2011# And #1/10/2011# i hav a query as above, the date i stored in the table was dd/mm/yyyy, but the format for query above is...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.