Update Query for text data in YYMMDD format into a Date

[SealM]

I've been all over the web for quite a few hours now, and I'm really close to solving this. But I cannot put all the pieces together on this one, and there is not ONE source which has a complete solution on how to do this. Unfourtunately, I'm still pretty new at Access, and this doesn't seem that difficult, but it still eludes me. Here's the problem:


I import a TXT file with a column that has a date. The date is formated YYMMDD, so 130115 would be 1/15/2013. The column can honestly be in text or number format, I'm not sure which to start from to make it easier, so assume it can be in either for the sake of the simplest solution. I want to make an UPDATE query that takes this column and creates a NDATE column with the date formatted correctly as 1/15/2013.

Pretty simple, but so far I have not found a complete SQL UPDATE query code that does this.

given field old_date (text or number formated) containing 130115, how do I make column new_date as a date field containing 1/15/2013?

Please and thank you, and thank you from the future newbs as myself who will find this someday! hehe

[JoeM]

If you are importing this text file into Access, you can actually import it as a date, even though it looks like YYMMDD. When going through the Import Wizard, just click on the Advanced button, and under the Date Order argument, select YMD. Then choose the Date Data Type for that field, and it should work!

[jzwp11]

Are trying to run the update query after you have already imported the data and put it in a table? If so, what is the data type of the field that you are putting the YYMMDD information into? If that field has a text data type then you will only need to worry about text data not numeric. The NDate field you mention has to be a date/time datatype. What is the range of the years in your data?

[SealM]

Thanks for the replies.
JoeM,
I have tried multiple ways, but when I import this under the advanced tab, just as you mentioned, it always comes up with a data conversion error. This is ironic, becuase when I import this field into Excel, and pick the YMD format, it comes up perfectly with the proper date in Excel, so I was very frustrated when I could not make it happen in Access. This is why I'm resigned to the idea of using a query to repair it after it's imported.

zqwp11,
It is presently in a text field (although I could import it and make it a numeric field if it makes the conversion/update any easier).
The data type for the new field would be date/time. I've created the field in the table, but I don't have the SQL statement that would update it.
These are date ranges that come from years 2012-2013.

I can quote a few people's work I've tried to use, and it gets things REAL close, but most of the examples I've ran into are using the mmddyy format. Any help would be fabulous! Thanks again.

[SealM]

Here is some work on this site that is basically the same thing I'm trying to do. I just don't know what his SQL code would have been, and what is the field property of the date he STARTS with when doing this?
https://www.accessforums.net/queries...date-1036.html

[JoeM]

If you are bringing it in as a Text field and want to convert it to a Date using an Update Query, couldn't you just use the DateSerial function in conjunction with Left, Right, and Mid functions to convert it to a date. Should be pretty straightforward.

Note that you will need two fields. If your Data Type for your date field that you are importing into is Text, and you want to change it to a Date, you are going to need another field that has a Date Date Type to write your results too (I don't think you can write it back to the same field).

[jzwp11]

I worked out this query. You will have to substitute your table name in place of table1 and your current text field for txtDate. I used ndate as the field name to be updated to correspond to what you said in your post.

Update table1 SET ndate=DateSerial(IIf(Left(txtdate,1) In ("0","1","2"),"20" & Left(txtdate,2),"19" & Left(txtdate,2)),CInt(Mid(txtdate,3,2)),CInt(Right (txtdate,2)))

This part IIf(Left(txtdate,1) In ("0","1","2"),"20" & Left(txtdate,2),"19" & Left(txtdate,2)), of the query checks the first character of your textdate field and if it begins with 0, 1 or 2 it assumes it as occurring between the year 2000 and the year 2029. If the first character is something other than those it assumes that the year is between 1900 and 1999.

[SealM]

jqwp11,
Your complete SQL did the trick. It works, and without flaw.

JoeM,
Thanks for the information. I believe you would have gotten me on the right path, and I've tried using the dateserial function, but without success to date.

Anyway, marking this one as solved, thanks again!!

[jzwp11]

You're welcome.