473,390 Members | 1,387 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,390 software developers and data experts.

How to Convert Text Dates to Date format

32,556 Expert Mod 16PB
This thread is a response to a very popular thread (How to convert Text dates to Date format?) which is locked down now so I thought I'd reply separately. This should be considered as a continuation of the topic where, hopefully, I can clarify any confusion.

Always bear in mind that date values and date literals are completely different animals. Literal DateTimes and Their Delimiters (#) may help with the understanding.

A date value, with or without the date or time portions, is stored internally (In most systems.) as a double-precision count of the number of days past a certain point in history. Whenever you see a date/time value displayed it is almost certainly after the value has been formatted. It's really very important to understand this point.

A date literal, as you may find in a value used within SQL for instance, will be a formatted string. As you'll see from the linked article above the formatting can vary considerably - even within SQL - but it's always a string.

Most of the problems I've seen questions about can all boil down to the simple confusion of the person with the problem treating something as one type (Double or String) when they really should have treated it as the other. IE. Get that right in your head and you're very unlikely to experience problems.

From a date value (Double) you can select one part and exclude the other by using two of the VBA functions (The library VBA as opposed to the language.) :
Expand|Select|Wrap|Line Numbers
  1. DateValue()
  2. TimeValue()
These each return just the part specified in the name.
Oct 23 '21 #1
4 20591
2 2Bits
You can convert text date to date format by using following function procedures.
Expand|Select|Wrap|Line Numbers
  1. DateValue()
  2. TimeValue()
Nov 3 '21 #2
32,556 Expert Mod 16PB
Why would you send a reply that is only a small part of the answer when the question has already been dealt with in full?

I take time to read what's been added but nothing whatsoever has been added. Just a small part copied - and even that had to be corrected.
Nov 4 '21 #3
1 Bit
Using the DATEVALUE function is a basic method to convert a date into an actual date which is stored as a text.
Nov 9 '21 #4
32,556 Expert Mod 16PB
Hi Leo.

While you haven't added anything to the discussion that wasn't already there and properly described in the original article, you have managed to introduce some confusion in what you've posted. Let me see if I can clarify that so that nobody gets confused. Generally speaking of course, it's better to read the original before replying and only reply if you have something to add that may be helpful.

While code that uses the DateValue() function in VBA will return a date value if a string value is passed, it isn't the fact of calling the function which ensures the string value is converted to a date value. It does have input in as much as the parameter expected being specified as Date/Time ensures the compiler will recognise that the value expected is a Date/Time (Technically a Date type but in VBA this handles both dates & times of course.) and therefore do a conversion for you before the value is passed to the function.

Essentially the DateValue() function will be passed a value that has already been converted to Date/Time if the value passed is capable of being interpreted that way. It will not even have any way of determining that the original value was different from what it was passed.

VBA does a lot of conversions for you automatically. It's a concept of the language.
Nov 16 '21 #5

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

Similar topics

by: ggnanaraj | last post by:
In AS/400, have a table that has a character field defined. This has to be converted to date format of YYYY-MM-DD. The sample character data is as follows: 02/05/2005. In UDB, you can do a...
by: deko | last post by:
After importing text date fields, the dates look like this: 12/31/2003 8:00:00 AM I'm having trouble working with these dates with Date(), Now(), etc, -- does not seem to match these dates....
by: Nimmy | last post by:
Hi, I have a file which has different dates, I want to scanf them as CHAR and convert them to DATE format, how can I do this? Thanks
by: megala | last post by:
Hi How to convert a string "11022005" to a specific date format-02/11/2005
by: stevag | last post by:
I want to convert a custom date format of the type DD/MM/YYYY into the RFC 822 date format in order to use it for RSS feeds. I want to do this transformation into an XSLT transformation which is...
by: Twanne | last post by:
Hi, I've got a field in textformat with a date that looks like this: 2005-01-03 22:43:19.65 Now I need to move this field to another field in date format. So far I tried to use IsDate() that...
by: ITCraze | last post by:
Thank you debasisdas for replying. But u know I have t convert the txt file into csv through C# code.My application requires that I have to select one text file from the Browse and...
by: Gana st | last post by:
How to convert string (date stored as string) to date and how do i add 1 year?
by: DAHLKA | last post by:
I have a legacy date stored in a text field in DDMMMYYYY format that I need to convert to a date in MM/DD/YYYY format in Access. Example, convert text stored as 10JAN2017 to date in 01/10/2017...
by: jdusn1978 | last post by:
Goal: Convert text to date in a query and show only dates that are older than 60 days from Date() Iíve searched the site and nothing quite matches what Iím looking for, at least in a way I can...
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
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: 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: 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 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.