none
Convert text to date

    Question

  • Hi All,

    I have a linked table in Access that has data stored as text in a column like:

    2/17/2010 12:00:00 AM

    How can I convert this so that I have an actual date(not a text string)
    with the format of 2/17/2010  ?  I am trying to create a query that will
    pull data in from between two dates that are selected by the user using
    DateTimePickers on a VB form.

    Your help is greatly appreciated!
    -Diana

    Thursday, February 10, 2011 7:32 PM

Answers

  • In Access you can use CDATE function to convert text into date, so your Query in Access will be something like

    select * from myAccessTable where cdate(myTextDateColumn) between #2/17/2010# and #10/17/2010# 

    BTW, for Access database related questions this forum

    http://social.msdn.microsoft.com/Forums/en-US/accessdev/threads

    seems to be a better place.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Di.Miller Friday, February 11, 2011 3:38 PM
    Thursday, February 10, 2011 8:13 PM

All replies

  • create table #a
    (dt text)


    insert into #a values ('2/17/2010 12:00:00 AM')



    select convert(datetime, convert(varchar(30), dt)) from #a
    Thanks, Cool Mind -- If you find my answer helpful, please mark it as Answer.
    Thursday, February 10, 2011 7:56 PM
  • Hi Cool,

    I don't understand what you are doing here. Can you please explain in more detail?  There are hundreds of rows in the database.  How do I insert this information into a new table and convert it?

    Thanks for your patience,

    Diana

    Thursday, February 10, 2011 8:03 PM
  • as you explained to us before, that

    date is in TEXT format.

     

    you just need to convert TEXT into VARCHAR first

    and from Varchar to Date.

     

     


    Thanks, Cool Mind -- If you find my answer helpful, please mark it as Answer.
    Thursday, February 10, 2011 8:04 PM
  • In Access you can use CDATE function to convert text into date, so your Query in Access will be something like

    select * from myAccessTable where cdate(myTextDateColumn) between #2/17/2010# and #10/17/2010# 

    BTW, for Access database related questions this forum

    http://social.msdn.microsoft.com/Forums/en-US/accessdev/threads

    seems to be a better place.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Di.Miller Friday, February 11, 2011 3:38 PM
    Thursday, February 10, 2011 8:13 PM
  • Hi Naomi,

    Can I create the query with two variables that are from datetimepickers?
    I have one that is for StartDate and the other is EndDate so I am thinking it
    would be something like:

    SELECT * FROM Plates_Query WHERE CDATE(Field3) BETWEEN StartDate AND EndDate

    Or, would it be better to use the StartDatePicker.Value and
    EndDatePicker.Value  instead of the declared variables?

    Am I going in the right direction here?

    Thanks for your insightful help,
    Diana

    Thursday, February 10, 2011 8:47 PM
  • I think you're close, but you probably need your StartDate and EndDate as parameters. I think in Access it will be either

    select * from Plates_Query where cdate(Field3) between ? and ?

    Or

    select * from Plates)Query where cdate(Field3) between @StartDate and @EndDate

    but using @ for parameters is SQL Server syntax and I'm not 100% sure if it works for Access or not.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, February 10, 2011 8:53 PM
  • Hi Naomi,

    I am going to try to use parameters which I have used sucessfully in the past
    with Access. Partial code below:

    Dim Plates_Query As String = _
    "SELECT *" & _
    "FROM [PlateHistory_Query] WHERE CDATE(Field3) BETWEEN (Begin = ?) and (End = ?)"

    PlatesDA.SelectCommand.Parameters.AddWithValue("@Begin", StartDate)
    PlatesDA.SelectCommand.Parameters.AddWithValue("@End", EndDate)

    I'll let you know how it goes.  Thanks again for being so helpful!
    -Diana :o)

    Thursday, February 10, 2011 9:04 PM