Link to home
Create AccountLog in
Avatar of Member_2_1316035
Member_2_1316035

asked on

Convert timestamp text field to short date

I am pulling a field from an external data source that contains time stamp data stored in a text format field.

2017-05-15 00:00:00.0000000

I need to format this into a short date to run some date comparisons (e.g.; this date is greater than today's date).  

Using the "mid" and "left" functions I was able to extract the date into the date format I needed (e.g.; 05/15/17).  I then applied the "format" function in hopes it would bind this data as a short date format.  

Format((Mid([SESSIONDEFAULTSTART],6,2) & "/" & Mid([SESSIONDEFAULTSTART],9,2) & "/" & Left([SESSIONDEFAULTSTART],4)),"Short Date")

The function returns the correct appearance of short date format, but it doesn't seem to be an actual "short date" because when I order the column it is only sorting by month.  Or if I compare it against Date() it doesn't seem to be comparing two dates.

Thanks for your help.
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Member_2_1316035
Member_2_1316035

ASKER

YES - PERFECT!