Strip Time of a Date?

[justinf]

Hello! I'm looking for help on how to strip time data of my date string. The information in each field is extracted from another type database into access.

For example:

12/31/2007 12:34:56:789
2/2/2007 00:00:00

I would want to be
12/31/2007
2/2/2007

Another problem is that the dates are different sizes ranging from 8-10 Characters long. I've tried using left/right/len and replace functions. As well as Sql statements to adjust the format from text to DATETIME. When I try not all the fields switch over.

Been working on this problem for awhile. I was able to make a macro in Excel to accomplish this task. However I want to be able to do this in ACCESS for automation purposes. Thank you for the help!

Using ACCESS 2000-2003. Various computers.

[Corster]

Have you tried just formatting as a date?

Format(YourVariableOrObjectValue, "dd/mm/yyyy")

[justinf]

Have you tried just formatting as a date?

I have tried using Format in a query. As well as using Alter table/Column lines in ACCESS SQL. When I try those, some records get erased. namely those that are like e.g.

2/2/2007 12:34:56:789

I am thinking access doesn't recognize the time format. I am not for sure. I'm dealing with around 12k plus records, and unfortunately the Dates in each of the fields are important for further database functionality down the road. I appreciate the feedback, and i'll be still working on it. Thank you!

[pks00]

use DateValue

so DateValue(mydatevariable) returns the date portion of a date/time value

[justinf]

use DateValue

so DateValue(mydatevariable) returns the date portion of a date/time value

I will give it a shot. Will report what happens. Thank you!

[justinf]

I will give it a shot. Will report what happens. Thank you!

I tried it out. It does work for some of the values. However some values still get erased. Still working on it. I used it in a query format:

datevalue([start_dt])

Thank you for the help!

[justinf]

Trying a different route...

Ok. I'm going to try a different route. So far so good. Just need to try it at work now. Instead of trying to find and replace data in Access, i'm going to use Excel from Access. I've been piecing together ideas from various resources. Finally found something that works... well at home anyways.. Code to follow.

 
Public Sub GetExcel()
Dim xlApp As Excel.Application
Set xlApp = Excel.Application
xlApp.Visible = True
Workbooks.Open Filename:="filename"
    Cells.Replace "??:??:??:*", " "
    Cells.Replace "??:??:??", " "
    xlApp.SaveWorkspace
 
    End Sub
 

Hopefully I have everything right. At least it seems to work. Appreciate any and all input. And of course. Thank you for the help!

[NeoPa]

Are you sure the time part comes as "??:??:??:???" rather than "??:??:??.???"?
The former doesn't make sense as the last three digits should be fractions of a second and not a separate subfield. If your data is formatted as the former then that is why you'll have problems with it.

[justinf]

Are you sure the time part comes as "??:??:??:???" rather than "??:??:??.???"?
The former doesn't make sense as the last three digits should be fractions of a second and not a separate subfield. If your data is formatted as the former then that is why you'll have problems with it.

Unfortunately that is the way the data comes out from another database. "??:??:??:???" I have no control over that. I simply export the data, then do the other tasks with it. However, when using the code above, it replaces the time data with a blank with makes the date information more usable for my purposes. Seems to work at work so far! I'm still new to using code for just about anything. Alot of experimenting going on. Appreciate the input, and the explanation of why i've been having so many problems for the past (several) months. Thank you!

[NeoPa]

No problem.
One technique I often use when data supplied to me from an external source wants to play silly-buggers, is to import it first into a temporary table whose design is very forgiving of any data found in the import.
From there I use an append query to load that data (fiddled where necessary) into the table I need the data in. This gives me a lot of power over how to handle data that's not already formatted how I would like it - converting strings to date/times; numerics etc where required.

[Corster]

How about stripping the last 4 numbers off?

Mid(CStr(YourDate), 1, Len(CStr(YourDate)-4))

Might work.

[pks00]

Hi JustinF, Sorry for the late reply. Im only a part-timer on thescripts so I dont always reply on time.

Now has this been sorted?

If u get any crap on the end of the date, Im thinking a vba function here to use in your query
eg

select ClearCrapOutMeDate(mydatevariable) as CleanDate
from mytable


and a function defined as

public function ClearCrapOutMeDate(sDate as String)
    Dim sLine() as String
 
    ClearCrapOutMeDate = sDate
 
    if Instr(1, sDate, " ") > 0 then
        sLine = Split(sDate," ")
        ClearCrapOutMeDate = sLine(0)
    end if
end function
 

[NeoPa]

You might want :

Public Function ClearCrapOutMeDate(sDate As String) As Date

as the first line though. A small change.

[pks00]

You might want :

Public Function ClearCrapOutMeDate(sDate As String) As Date

as the first line though. A small change.

I left it as variant just in case invalid string is passed i.e. something that isnt a date or doesnt have a space.

[NeoPa]

Oh OK. Fair point.
I suppose the OP can choose which version suits their situation best ;)

[justinf]

I appreciate all the ideas. When I get a chance i'll play with those ideas to see if they work. So far so good with tapping into excel at the moment. I'm very far from being proficient however getting better everyday! I really appreciate the help! Thank you all!