473,881 Members | 1,711 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Conversion of date/ time fields

I have adatabase which daily imports data from a spreadsheet to a
named table. The spreadsheet contains one filed which is date AND time
whereas in the table I have one field being date which is formatted
to short date,.The data looks OK in the table with only a date showing
but wen I examine the date field I can expand it to see also the time.
This is causing some problems with other routines using the table.
Can anybody advise how I can actually REMOVE the time details from the
field so that I have only a date field ?

Thank you
Gerry

Oct 3 '07 #1
2 1782
Use an Update query to remove the time part of the field.

Example:
UPDATE Table1
SET Field1 = DateValue(Field 1)
WHERE Field1 Is Not Null;

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"GerryWiles " <ge***@colvan.c o.ukwrote in message
news:11******** **************@ 22g2000hsm.goog legroups.com...
>I have adatabase which daily imports data from a spreadsheet to a
named table. The spreadsheet contains one filed which is date AND time
whereas in the table I have one field being date which is formatted
to short date,.The data looks OK in the table with only a date showing
but wen I examine the date field I can expand it to see also the time.
This is causing some problems with other routines using the table.
Can anybody advise how I can actually REMOVE the time details from the
field so that I have only a date field ?

Thank you
Gerry
Oct 3 '07 #2
On Oct 3, 5:52 am, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:
Use an Update query to remove the time part of the field.

Example:
UPDATE Table1
SET Field1 = DateValue(Field 1)
WHERE Field1 Is Not Null;

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"GerryWiles " <ge...@colvan.c o.ukwrote in message

news:11******** **************@ 22g2000hsm.goog legroups.com...
I have adatabase which daily imports data from a spreadsheet to a
named table. The spreadsheet contains one filed which is date AND time
whereas in the table I have one field being date which is formatted
to short date,.The data looks OK in the table with only a date showing
but wen I examine the date field I can expand it to see also the time.
This is causing some problems with other routines using the table.
Can anybody advise how I can actually REMOVE the time details from the
field so that I have only a date field ?
Thank you
Gerry- Hide quoted text -

- Show quoted text -
Or, you could just format it for date. format(myDate," mm/dd/yy") or
select "Short Date" in format property.

Oct 3 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
2194
by: nimish | last post by:
I have Month, Day and Year fields on my form. When I submit this form I want to create a valid date from all three fields before inserting into MYSQL table. I tried following: $dob=$Month."-".$Day."-".$Year; $dob = strtotime($dob); But it does not update my date field in the table. I am novice in PHP, do you know where i am making a mistake.
3
3750
by: praba kar | last post by:
Dear All, I have doubt regarding date string to time conversion function. In Python I cannot find flexible date string conversion function like php strtotime. I try to use following type function 1) parsedate function failed if a date string like below format "16-MAY-2005 01:26" 2)parsedate_tz function also failed if a date string
1
11692
by: heirou | last post by:
I'm a novice in this subject....I've made a database that requires a time conversion. For example, if local time is 1200, determine the time in Korea. I use two fields: a date field, and a time field. I need the converted time to show up in a report. I also need the corresponding date to increment if necessary. Any ideas on how to do this would be greatly appreciated.
8
2253
by: Ishbel Kargar | last post by:
Since upgrading from old laptop (Windows 98) to new laptop (Windows XP), my mail-merge letters are doing strange things with date formats. For instance, my reminder letter for lapsed subs carries the 'expiry' date as a merge field from the database, and this previously was shown as dd/mm/yy (UK format). Now it persists in showing as mm/dd/yy. I've gone to the Windows Control Panel and made sure that my Regional settings show UK format,...
8
2083
by: William Bradley | last post by:
First of all I have been working with Access 97 and this morning the owner of the business phoned me to inform me that he had updated to Access 2000 and parts of my forms would not work anymore. On my form I have a field "Expiry Date".When I put the following into the AfterUpdate field, it adds 183 days to the current date, and displays it in the "Expiry Date" field. Me.ExpDate = DateAdd("d", 183, Me.Date)
1
3749
by: Shahzad | last post by:
Dear Respected Gurus/Sirs, I would like to share your ideas and get reviews will be highly appreciated in anticipation Question No.1 I had developed a seprate audit/security database that stored userID,Password,Username to track who has logon/logout, and what actions had performed(add, edit, delete, update etc). now the question who to encrypt the data, what mehtodology is best where at least
4
2065
by: Annie D via AccessMonster.com | last post by:
Problem! I have a series of CSV files that I am bringing in to Access, contained within each of them are 6 date fields, however, they are not being recognized as dates…this is a big problem! the date looks like this: 20050816 23:59:59.000 I wrote a query to split up the numbers and then bring them back together in the right order:
8
3658
by: John Wildes | last post by:
Hello all I'm going to try and be brief with my question, please tell me if I have the wrong group. We are querying transaction data from a DB3 database application. The dates are stored as text fields. Each date for example 10/31/03 or October 31st 2003 is stored as 10/31/A3 in the system. My reasoning for this is because they couldn't solve their Y2K problem or this is their solution to it. All dates prior to 2000 are stored...
5
2136
by: Ivan Velev | last post by:
Hello, Minimal example below - it gives me different output if I comment / uncomment the extra time.mktime call - note that this call is not related in any way to main logic flow. When "problematicStamp = ..." is commented I get gmtStamp: 1130634600.0
0
9776
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11104
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10724
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10403
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7113
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5781
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4598
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4198
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3226
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.