Convert Date text string to Date format (1 Viewer)

[lread123]

Hello,

OK I have been going in circles, and I hope someone can help!?!

I am connecting acces to oracle servers via ODBC .
in the table there is a date time stamp - format data type text:
20130225060621

I would like to convert so that it is recognised as a date so I can calculate against e.g. Now() or another standard date format.

this is what I am currently using (which is OK) BUT I need to calculate against the time also.

Current Check Point Date: CDate(Mid([DZ_ZPKT_AK],7,2) & "/" & Mid([DZ_ZPKT_AK],5,2) & "/" & Mid([DZ_ZPKT_AK],1,4))

I spoke to an IT guy who works in TOAD program and he gave me this SQL:

select to_date(dz_zpkt_ak,'DD/MM/YYYY HH24:MI:SS')from orders where ordnr='4411310';

this is the format I would like but cant make this work.

Help!

Lance.

 

[plog]

CDate will do that. You just need to feed it the time as well. For example:

DateTimeField: CDate("4/10/2013 4:23 am")

Will return a date/time field with all that information.

 

[lread123]

Hi

Many thanks for the reply - OK so I have tried:

test: CDate([dz_zpkt_ak],'DD/MM/YYYY HH24:MI:SS')
and test: CDate([dz_zpkt_ak],"DD/MM/YYYY HH:MI:SS")

but just get error... what am I doing wrong?

 

[plog]

CDate (http://www.techonthenet.com/access/functions/datatype/cdate.php) only takes one argument, you are trying to pass it what looks like a format option. That's not a choice.

 

[lread123]

Sorry but not getting this....
In the table I am selecting in the query, the value is text 20130225060621
this is a date and time YYYYMMDDHHMMSS

I want to, in the query, select the 1000's of values in column named dz_zpkt_ak

If I do nothing I get all the date values in the text format. But I want a date/time format like this DD/MM/YYYY HH:MM:SS

I've tried this
test: CDate(Mid([DZ_ZPKT_AK],7,2) & "/" & Mid([DZ_ZPKT_AK],5,2) & "/" & Mid([DZ_ZPKT_AK],1,4))

Can you please explain how I can use your suggestion:
DateTimeField: CDate("4/10/2013 4:23 am") so that all the value are returned in the required format?

Sorry for being a tad slow here...

 

[plog]

I've tried this
test: CDate(Mid([DZ_ZPKT_AK],7,2) & "/" & Mid([DZ_ZPKT_AK],5,2) & "/" & Mid([DZ_ZPKT_AK],1,4))

You're really throwing me with your experience level. Did you write the above code? Or is it something you pasted from the internet? Do you know what it is doing? Specifically the Mid() calls?

 

[lread123]

Yes I wrote the code and yes I understand it.

My experience level - I am not bad, lets say!

Are you sating that I need to expand the mid statement?
If so, I have tried and couldn't get the desired result - it wouldnt join the space hh & ":" to return HH:MM:SS

can you please help expand?
Thanks...

 

[plog]

Evaluate what you are currently putting inside the CDate() function:

Mid([DZ_ZPKT_AK],7,2) & "/" & Mid([DZ_ZPKT_AK],5,2) & "/" & Mid([DZ_ZPKT_AK],1,4))

Suppose [DZ_ZPKT_AK]=20130225060621. The above resolves to: "25/02/2013". If you want the time to be included, well, you should include the time.

 

[lread123]

OK thanks I will try again; as I said though, I have tried and couldn't get the desired result - it wouldnt join the space hh & ":" etc.... to return HH:MM:SS.

I give it a go at work tomorrow, and let you know how I get on.... Thanks for everything so far.

 

[Brianwarnock]

You could always try Dateserial + timeserial

Brian

 

[lread123]

Hi Brian, thanks for the suggestion.
I have looked at some of your old posts and each one makes sense to me !

But a question would be how to join date and time? Hopefully just a '&' !!
I'll feed back tomorrow... Thanks

 

[Brianwarnock]

If & doesn't work try + as I wrote it in the post.

Brian

 

[lread123]

Hi thanks to you both I have fixed my date time!

Final statement is:
endDate: CDate(Mid([DZ_ZPKT],7,2) & "/" & Mid([DZ_ZPKT],5,2) & "/" & Mid([DZ_ZPKT],1,4))+CDate(Mid([DZ_ZPKT],9,2) & ":" & Mid([DZ_ZPKT],11,2) & ":" & Mid([DZ_ZPKT],13,2))

It was the '+' that I didnt know about! happy now!

 

[nyaworld]

Hi thanks to you both I have fixed my date time!

Final statement is:
endDate: CDate(Mid([DZ_ZPKT],7,2) & "/" & Mid([DZ_ZPKT],5,2) & "/" & Mid([DZ_ZPKT],1,4))+CDate(Mid([DZ_ZPKT],9,2) & ":" & Mid([DZ_ZPKT],11,2) & ":" & Mid([DZ_ZPKT],13,2))

It was the '+' that I didnt know about! happy now!

I'm a newbie. I'm trying to accomplish what you did. The field I'm trying to convert from text to Short Date is called [Start Time]. Currently the value in this field is 17:30:00.000 (which I want to convert to 5:30PM (not military time)).

Can you help me rewrite your expression so that it makes sense when no date is found in text string?

Start Time New: CDate(Mid([Start Time],7,2) & "/" & Mid([Start Time],5,2) & "/" & Mid([Start Time],1,4))+CDate(Mid([Start Time],9,2) & ":" & Mid([Start Time],11,2) & ":" & Mid([Start Time],13,2))

:banghead:

 

[arnelgp]

you can always use format:

Start Time New:CDate(FORMAT([start time],"0000-00-00 00\:00\:00"))

 

[nyaworld]

I tried this:

Start Time New:CDate(FORMAT([start time],"0000-00-00 00\:00\:00"))

and I get "#Error" when I run query.

 

[nyaworld]

you can always use format:

Start Time New:CDate(FORMAT([start time],"0000-00-00 00\:00\:00"))

I get #Error run when I run the query


************Never mind. I used this in my control source and it worked!

=CStr(Format(CDate(Left([Start Time],2) & ":" & Right([Start Time],2)),"hh:nn AM/PM"))



This took 17:30:00:000 and made it show up in my form as 5:30pm . That's what I needed.

Thank you all for your help.