Convert text to date

Question

I've got a value from a table with a result of 20100518.1300

How can I convert this to a date time format in a query?

Answer 1

Is this a Number (Double or Decimal) or a text string? You can use either but the syntax is a bit different. If it's Number try

DateSerial([numdate] \ 10000, [numdate] \ 100 MOD 100, [numdate] MOD 100) + TimeSerial(100 * ([numdate]-fix([numdate]) MOD 100, 10000 * ([numdate]-fix([numdate]) MOD 100)

For text, try

cdate(Left([textdate], 4) & "-" & Mid([textdate],5,2) & "-" & Mid([textdate],7,2) & " " & Mid([textdate],10,2) & ":" & Right([textdate], 2))

There are dozens of ways to do this using the CDate, DateSerial, TimeSerial and substringing functions, but I did test these and they both work.

---

John W. Vinson/MVP

Answer 2

You could do something along the lines of:

Format(Replace("20100518.1300",".",""), "0000/00/00 00:00")

Daniel Pineault

http://www.cardaconsultants.com

MS Access Tips and Code Samples: http://www.devhut.net

Answer 3

[YourText] = the field with the text --

DateSerial(Left([YourText],4),Mid([YourText],5,2),Mid([YourText],7,2))+Mid([YourText],10,2)/24+Mid([YourText],12,2)/1440

---

Build a little, test a little.