Hi,
Can't seem to find a work around for what I thought would be something easy to do. I have a table that contains 3 columns of date values. The date values most often, but not 100%, include the time. I would like to truncate these values so that they are date only. By that, I meant that I do not wish to format or otherwise hide the time, I want to round off the day and eliminate the time.
For example 03-Dec-07 16:00 would return 03-Dec-07 (not 04-Dec-07 since the time was in the afternoon). Even if there is no function, I thought I could somehow subtract the time from the whole string but I'm just not putting it together correctly. Can someone tell me what simple thing I'm not doing???
Thanks!
(Sorry, I should probably have mentioned that I'm doing this in an Update Query)
3  26995 
Try: - Format([YourDateField],"dd/mmm/yy")
Regards,
Scott
Thanks, well I knew it would be simple! I saw the Format function, but I thought it was a 'cosmetic' format similar to what you do in Design View and would retain the time.
Interesting in all the researching I did, I never saw a warning that if you use this option, you will lose data permanently if you do not call it all out. That's really good to know.
Thanks for the help!
Thanks, well I knew it would be simple! I saw the Format function, but I thought it was a 'cosmetic' format similar to what you do in Design View and would retain the time.
Interesting in all the researching I did, I never saw a warning that if you use this option, you will lose data permanently if you do not call it all out. That's really good to know.
Thanks for the help!
You're quite welcome! Glad to help...
As far as losing data, that will only happen in certain situations, like what you are attempting with an update query. You can also use the Format function in display only situations, where it provides data to a control to display, in which case it will not touch the stored data, only will pass it in formatted form.
Regards,
Scott
Sign in to post your reply or Sign up for a free account.
Similar topics
by: vnl |
last post by:
I'm trying to run a SQL query but can't find any records when trying to
select a certain date. Here's the sql:
SELECT field 1, field2, date_and_time,
FROM table1
WHERE date_and_time =...
|
by: Subodh |
last post by:
Hi,
I need to purge date of a table with 290 million records up to 50%.
I'm planning to do in chunks but also needs to truncate the
Transaction log manually(in a SP) after every purge operation....
|
by: martin |
last post by:
Hi,
We have a heavily used production server and a table which logs every
hit on a web site. This table has grown large over time and we want to
clear it down as efficiently as possible. We would...
|
by: New MSSQL DBA |
last post by:
I have recently been assigned to take over several MSSQL environments
and found some of the existing practice confusing. As most of my
previous experiences are on Oracle and Unix platform so would...
|
by: meltedown |
last post by:
Why doesn't this return anything ?
SELECT DATE_SUB('FROM_DAYS(TO_DAYS(2005-09-28 18:04:19))', INTERVAL 6 DAY)
|
by: Nick M |
last post by:
Hello All,
Excellent info here Thanks!
I am very new to using access in general and I am on a learning curve.
I'm trying to import an excel workbook (with worksheets) into an access db
via a...
|
by: Eddie |
last post by:
I have a DataTable.Select() statement with a DateTime field that is
being compared to a Date:
Dim drCal as DataRow() = tblCal.Select("CalendarItemDate
='12/1/2003'", "CalendarItemDate",...
|
by: Sumanth |
last post by:
Are there any implementations of truncate in db2. Is it going to be
implemented in the future?
Is there an alternate way of doing a truncate of a table that has a high
record count without using...
|
by: Troels Arvin |
last post by:
Hello,
Until this date, I believed that DB2 has no TRUNCATE TABLE command. But
then I came across...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
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
|
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...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
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...
|
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,...
|
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,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
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,...
| |
