Access 2010 Changing String to Date with Leading Zero

Hello,

The Excel extract I'm linking to has a date column in string format (ex. 12312016).  Furthermore, when it gets to the first 9 months of the year it does not add the leading zero (ex. 1152017 instead of 01152017).  How do you recommend converting this information into a date with a leading zero for the first 9 months?

Thanks!
LVL 1
EscanabaAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
Use this query:

SELECT 
    tbl_date.date,
    CDate(Format(Right("0" & CStr([date]), 8), "@@/@@/@@@@")) As TrueDate
FROM 
    tbl_date;

Open in new window

/gustav
Test--1-.accdb
0
 
ste5anSenior DeveloperCommented:
You need to pad the string: Right("0" & value, 8).
0
 
Rey Obrero (Capricorn1)Commented:
after importing, run an update query, something like

update tableName
set [dateField]=format([datefield],"00000000")
where len([dateField])=7
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
EscanabaAuthor Commented:
I should of noted that I am really new to Access so with either suggestion can you guide me as to where those formulas should be added to the query?
0
 
Rey Obrero (Capricorn1)Commented:
what is the name of the table?
what is the name of the field with date?

better if you can upload a copy of your db.
0
 
Gustav BrockCIOCommented:
Use a query like this:

Select 
    *,
    CDate(Format(Right("0" & [YourStringDateField], 8), "@@/@@/@@@@")) As TrueDate
From
    YourTable

Open in new window

/gustav
0
 
EscanabaAuthor Commented:
Unfortunately I cannot attach the database due to confidential data.  The table name is CtResults_Masters and the field name containing the date is named 'Date'.  Note that this is a linked table and the date field is coming in as a number data type.  It will not allow me to change the data type.  If I change the format to mm/dd/yyyy it changes the data.  So 12262016 becomes 12/30/1899.  I've attached a sample database with just the date field so any guidance on putting recommendations in place using the sample file would be helpful.
Test.accdb
0
 
ste5anSenior DeveloperCommented:
Sure , you can. It's called a concise and complete example.. just craft such a sample showing your problem.
0
 
EscanabaAuthor Commented:
ste5an - oddly enough I thought the sample provided was sufficient.  Apparently not far off the mark based on Gustav's ability to provide a solution.
0
 
EscanabaAuthor Commented:
Thank you very much for your patience and assistance.
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.