Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Posts
    4

    Unanswered: "Converting numbers into Dates"

    I'm working in Access 2000. I was given some tables with number (long integer) data types for dates, such as 20041011 for Oct. 11, 2004. How can I convert these rows of numbers into dates? in order to re-format the table to accept them as dates?

    I prefer to do this within the sql statement (update query, etc). What is the sql syntax to do this? (assume the column name is Notdate). Thanks!!!

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    For Access you can do it like this

    Code:
    UPDATE Tablename 
    SET [NotDate] = DateSerial(Int(Left([notDate],4)),Int(Left(Right([NotDate],4),2)),INT(Right([NotDate],2)));

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    It seems a better idea to me to create a second column D with datatype Date to store the result in:

    UPDATE tablename SET D = DateSerial(...
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •