none
How to Convert text formate date time to actually date time and compare

    Question

  • Hi,

    i have table , while inserting values in to table we are saving date time into updated_date column , this is text type. so value is saving in the format "2013-01-31-10.55.20.000000". for one query i have to compare date times. i am not getting how to convert this text value in to date time and compare in msaccess2010. any one have idea please help me out.

    Thanks in Advance,

    V.Venkat


    Regards white smile

    Thursday, February 28, 2013 7:02 AM

Answers

  • The easiest and the most transparent for further using way is to create a function like this:

    Public Function Txt2Date(s$) As Date
        s = Left(s, Len(s) - 7)
        s = Replace(s, ".", ":")
        s = Left(s, 10) & " " & Right(s, 8)
        Txt2Date = CDate(s)
    End Function

    And then just use this function in query like =Txt2Date([MyTxtField])

    If you don't want to use VBA, you can make a single expression right in the query itself:

    SELECT Left(Replace(Left([MyTxtField],Len([MyTxtField])-7),".",":"),10) & " " & Right(Replace(Left([MyTxtField],Len([MyTxtField])-7),".",":"),8) AS MyDateField


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    • Proposed as answer by KCDW Thursday, February 28, 2013 3:53 PM
    • Marked as answer by WhiteSmile Friday, March 01, 2013 5:23 AM
    Thursday, February 28, 2013 8:27 AM

All replies

  • The easiest and the most transparent for further using way is to create a function like this:

    Public Function Txt2Date(s$) As Date
        s = Left(s, Len(s) - 7)
        s = Replace(s, ".", ":")
        s = Left(s, 10) & " " & Right(s, 8)
        Txt2Date = CDate(s)
    End Function

    And then just use this function in query like =Txt2Date([MyTxtField])

    If you don't want to use VBA, you can make a single expression right in the query itself:

    SELECT Left(Replace(Left([MyTxtField],Len([MyTxtField])-7),".",":"),10) & " " & Right(Replace(Left([MyTxtField],Len([MyTxtField])-7),".",":"),8) AS MyDateField


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    • Proposed as answer by KCDW Thursday, February 28, 2013 3:53 PM
    • Marked as answer by WhiteSmile Friday, March 01, 2013 5:23 AM
    Thursday, February 28, 2013 8:27 AM
  •   Hi  Andrey ,

    Thanks  a lot  for your answers, its working fine.

    Thanks

    V.Venkateswara Rao


    Regards white smile

    Friday, March 01, 2013 5:25 AM