Convert text YYYYMMDD to date mmddyyyy 1

[CarpalT]

I have a field downloaded from another db that is a text field for a date in yyyymmdd order. I am trying to get it into Access date format so I can create a cross tab and a graph with it further down the line.
I used a make table query to convert the date using this expression
Dateval: (Val(Right([tdate],4)) & Val(Left([tdate],4)))

However that makes a number field and I can't change it to date in the table without losing the data.
If make the expression
Dateval:CDate((Val(Right([tdate],4)) & Val(Left[tdate],4))))
in the query, the query returns #error.

 

[Tranman]

Hi,
CDate usually does better with strings than with numbers. If you just reorder the input field in a string like so:

myDate = mid(tdate,5,2) & "/" & right(tdate,2) & "/" & left(tdate,4)

then

Dateval = cDate(myDate)

you should be ok.

Good luck,
Paul

 

[CarpalT]

Thank you Tranman, you deserve a star!
I was trying to use cdate on a number as my big fat Que book says "Cdate converts a numberic value to a date value. format Cdate(NumValue)"
So I didn't even try it on a string - Ha!
Happy Friday

 

[Tranman]

Not quite Miller time in Kansas, but soon, VERY SOON!

 

[Travlnbard]

I have the exact same problem, but I do not know where to type the input field string referenced above. I would like to covert the fields permanently from text to date.

Confused in NY (a.k.a Cathy)

 

[Tranman]

Cathy,
Not to worry, this is a pretty easy fix.

You can't convert yyyymmdd text fields directly to date/time fields, so here's what you do:

Create a new date/time field called DDATE (or whatever).

*for our purposes, I'll refer to your text date field as TDATE

Create an update query that includes the old text date field and the new DDATE field.

In the "update to" row for the DDATE column, put:

CDate(Mid([TDATE],5,2) & "/" & Right([TDATE],2) & "/" & Left([TDATE],4))

Run the query.

After it's done, look over your converted data, and if it's ok, delete your old text date column, and rename the DDATE column to that name.

That's it.

Good Luck,
Tranman (Paul)

 

[Tranman]

Hi Again Cathy,
Something just crossed my mind (it wasn't a long trip).

If you're concerned about the position of the column in the table, it might be better to just update your old date column to the right format, then change the type to date/time.

First you'd run an update query with just the text date column, updating it to:

Mid([TDATE],5,2) & "/" & Right([TDATE],2) & "/" & Left([TDATE],4)

Then go into design view on your table, and change the type to date/time.

I just tested this, and it worked fine....

Paul

 

[CarpalT]

I imported the file into a table, then used a make-table query to make a new table from the file. In the query,(qryStep1ConvertDates I named it), I typed this expression in the Field: box. It creates a new field from the old field tdate. Hope this helps! -


Newdate: Mid([tdate],5,2) & "/" & Right([tdate],2) & "/" & Left([tdate],4)

The world is full of good people.

 

[skyel]

Can you update the fields through a sql statement? I would like to change a yyyymmdd into a mm/dd/yyyy.

Once that's done, I need to combine this column with a time column so it becomes a date time column...

can it be done in sql? or do I need to create a new column first.

Thanks! Tell me if this is really confusing!

 

[CarpalT]

SKYEL, someone smarter than I am probably can! But I'm not a programmer, so I make do with what I can accomplish with queries, formulas, macros, and the like. If I had to do it, I would probably try to expand the expression in my 8/14 post to combine the date conversion with a time stamp in one expression. I know that when I write an expression in a query, Access generates sql, and I have looked at it using the view sql menu selection, but that's as far as I get.

I'm going to bow out of the sql part of this conversation and leave it to the experts.


The world is full of good people.

 

[skyel]

Ok, I'm thinking of doing something like this...

sql = "UPDATE PRSUDataAll SET APPDATE = Mid(APPDATE, 1, 4) & "/" & Mid(APPDATE, 5, 6) & "/"
cn.Execute sql

sql = "UPDATE PRSUDataAll SET APPDATE = SET FIRSTAPP & SET APPDATE"
cn.Execute sql

where APPDATE is the yyyymmdd that I want to change in to yyyy/mm/dd and FIRSTAPP is the time, and PRSUDataAll is the Table name... I could be completely off track... I don't know...

 

[skyel]

OK, let's just forget I said anything... I'm going off on some tangent that you people probably don't need to be bothered about... Sorry...

 

[CarpalT]

Sorry I didn't mean to be a wet blanket! Just didn't want you to wonder why there would be no reply from me on that one. My intent is to observe and try to learn from someone else's answer to your question. Maybe this thread is old enough that you need to start a new one with your question. (And attract the attention of someone who REALLY knows something!)
Hope you get your answer - have a great day!

The world is full of good people.

 

[GoDawgs]

You can do it all in one step:

sql = "UPDATE PRSUDataAll SET APPDATE = Left(APPDATE, 4) & "/" & Mid(APPDATE, 5, 2) & "/" & Right(APPDATE, 2) & " " & FIRSTAPP

That should work fine...problem is that you are putting it into the APPDATE field, which is either a text field or number field, not a date field (and actually if it's a number field then this won't work since we're trying to put text in there). Basically what I'm saying is I'd suggest creating a new date/time field and appending the data into there instead of the APPDATE field. Hope that helps.

Kevin

 

[skyel]

Thanks for your help, but I'm still having some trouble. I wrote this sql statement:

sql = "UPDATE PRSUDataAll SET FIRSTAPPDATE = Left(APPDATE, 4) & "/" & Mid(APPDATE, 5, 2) & "/" & Right(APPDATE, 2) & " & FIRSTAPP
cn.Execute sql

where FIRTAPPDATE is my new field for date/time. I got this message:

Microsoft VBScript runtime error '800a000d'

Type mismatch: '[string: "UPDATE PRSUDataAll S"]'

Does this mean that the field data type and the data I'm trying to input are not the same? If so, what type should they be and how might I do this?

-Skye.

 

[GoDawgs]

Hmmm...well first off I'm not sure if the post did this or not, but there's a bit of a difference in your posted query...this part:

(APPDATE, 2) & " & FIRSTAPP

should be:

(APPDATE, 2) & " " & FIRSTAPP

Basically that's putting a space between the date and the time. Hopefully that solves it, if not then let me know.

 

[skyel]

Well, when I put the space and " in, I get a message saying there's an unterminated string constant at the end. So I tried to put a " at the end of the sql statement and it says that there's an expected end of statement after the " which is after the space...

 

[GoDawgs]

One last quick fix try...change the end to:

(APPDATE, 2) & " " & FIRSTAPP & ";"

 

[skyel]

I get "expected end of statement"

sql = "UPDATE PRSUDataAll SET FIRSTAPPDATE = Left(APPDATE, 4) & "/" & Mid(APPDATE, 5, 2) & "/" & Right(APPDATE, 2) & " "[here] & FIRSTAPP & ";"

... should I be using any single quotes?

Thanks for your patience!

-Skye

 

[skyel]

This works:

sql = "UPDATE PRSUDataAll SET FIRSTAPPDATE =" & Left(APPDATE, 4) & "/" & Mid(APPDATE, 5, 2) & "/" & Right(APPDATE, 2)& FIRSTAPP
//cn.Execute sql

I'm not sure why the // is in front of the Execute statement, but so far, no errors... I guess I'll find out when I try to read the data... Thanks for your help!