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?

thanks

Elton

Answers


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.


Try:

Format(Split("Tuesday, June 17, 2014", ",")(1), "dd/mm/yyyy")

Need Your Help

InvalidRouteException on MVC3 application with multiple areas

asp.net-mvc-3 exception login asp.net-mvc-areas

I have a MVC3 web application with one default area and one admin area. Both have their won home pages after logging in. After successfully logging in to the application if i try to access the admi...

What is the correct way to join two tables in SQL?

php mysql sql

I have two tables. The first table holds simple user data and has the columns

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.