Parse data and time string to Access Date value
The format "yyyy-mm-dd" is an ISO standard and when encountering it, CDate()
expects the "yyyy" part to be followed by "mm-dd", never by "dd-mm". So a date string in that format is unambiguous; it represents the same date value regardless of the user's locale. And CDate()
will comply.
Furthermore, there is no way to give CDate()
a date string formatted as "yyyy-dd-mm" and get the date value you intend back. So CDate("2011-02-01")
will always give you the date value #Feb 1 2011#
(regardless of your locale), even if you intended that string to represent #Jan 2 2011#
. And CDate("2011-31-01")
will throw a type mismatch error.
Also note that this only works for dates after the year 100. (See Heinzi's comment.)
Related videos on Youtube
waanders
Developer for 25+ years. Access, VBA, (X)HTML, CSS, ASP, JavaScript, SQL Server and MediaWiki. A bit R, MySQL, PHP and WordPress.
Updated on June 04, 2022Comments
-
waanders almost 2 years
How can I parse a date/time string into an Access Date object given a certain date- and time format?
I can use the CDate() function like this:
Dim StrDateTime As String Dim DtTest As Date StrDateTime = "2011-12-31 23:59:59" DtTest = CDate(StrDateTime) MsgBox DtTest
This works, Access recognizes the format, fine, but how can I absolutely be sure that this happens under all circumstances (e.g. Date/Time settings Regional Settings, Access version?). I would like to "tell" CDate my special date/time format.
Other option is this (but a lot of code):
Dim StrDateTime As String Dim IntYear As Integer Dim IntMonth As Integer Dim IntDay As Integer Dim IntHour As Integer Dim IntMinute As Integer Dim IntSecond As Integer StrDateTime = "2011-12-31 23:59:59" IntYear = Val(Mid(StrDateTime, 1, 4)) IntMonth = Val(Mid(StrDateTime, 6, 2)) IntDay = Val(Mid(StrDateTime, 9, 2)) IntHour = Val(Mid(StrDateTime, 12, 2)) IntMinute = Val(Mid(StrDateTime, 15, 2)) IntSecond = Val(Mid(StrDateTime, 18, 2)) DtTest = DateSerial(IntYear, IntMonth, IntDay) DtTest = DtTest + TimeSerial(IntHour, IntMinute, IntSecond) MsgBox DtTest
Other advantage of CDate(): it give a Type Mismatch error on a wrong date/time value. DateSerial + TimeSerial recalculates a new date and time, so "2011-12-31 24:59:59" becomes 01/Jan/2012 0:59:59.
-
waanders over 12 yearsWell, what is the definition of an "unambiguous format"? The fact that it's recognized on my computer with my settings doesn't necessarily means it will in every situation? Or am I wrong? Is "yyyy-mm-dd hh:mm:ss" (and other known formats) universal to all systems?
-
waanders over 12 yearsIs this documented somewhere? And is there no way to explicitly tell CDate() which parse format to use, something like
CDate("yyyy-mm-dd hh:mm", StrValue)
? -
onedaywhen
CDate()
honours the Regional Settings, regardless of version of Access. One disadvantage ofCDate()
is that it does not always give a Type Mismatch when an ambiguous text value does not match Regional Settings e.g. my settings are UK,Month(CDate("28/04/2011"))
correctly returns4
(April) but the absurd UK valueMonth(CDate("04/28/2011"))
also returns 4 when arguably an error would be more useful.
-
-
Can Sahin about 12 yearsNote that this only works for dates after the year
100
:CDate("0001-02-03")
yields the 1st Feb. 2003.