Convert long date stored as text to short date Access SQL
I am trying to convert a long date stored as text to a short date via MS Access SQL.
For example I have a table which parsed information from a website and one of the field is Tuesday, June 17, 2014. I want to run an update query in another table which takes this value and converts it to 17/06/2014.
Any help on what functions I can use please?
Building on @VBlades' example, but allowing the year to be different from 2014, assuming that other years might appear in the source data.
This will only work if the dates you wish to parse are formated consistently.
Paste this function into a vba module and call it from your query.
Function dateParser(datestr As String) As Variant Dim day_month, year, day_month_year As String day_month = Split(datestr, ",")(1) year = Split(datestr, ",")(2) day_month_year = day_month + ", " + year dateParser = Format(day_month_year, "dd/mm/yyyy") End Function
For date strings like "Tuesday, June 17, 2014" a VBA function like this
Option Compare Database Option Explicit Public Function ParseDateString(DateString As Variant) As Variant If IsNull(DateString) Then ParseDateString = Null Else ParseDateString = CDate(Split(DateString, ", ", 2)(1)) End If End Function
will convert the string to a true Date value. If you are running an update query and putting the resulting value into a Date/Time field in the table then you DO NOT want to convert the date to dd/mm/yyyy format. Just use the result of the function (the true Date value).
If you must convert the date to a string then use the unambiguous date format yyyy-mm-dd. If you convert to dd/mm/yyyy format, Access might mangle ambiguous dates and 12/06/2014 could be interpreted as December 6, not June 12.
Format(Split("Tuesday, June 17, 2014", ",")(1), "dd/mm/yyyy")