Converting Text to Date

[Sashaone]

Hi there,

I have a table with a field called 'dereg_date' which is formatted as text, an example is as follows:

2006-07-07 00:00:00.000

I want to be able to convert this field to a date. I have tried using a query with Cdate([dereg_date]), however it doesn't convert it and just returns #Error for all the records.

Has anyone got any ideas on this? I need this fixed fairly urgently as my superiors are getting quite impatient!

Many thanks in advance!!
Sasha.

 

[KeithG]

Try the DateValue function.

 

[Sashaone]

Hi there,

Thanks Keith, but I tried that and it comes up with the same #error

Frustrating!!...

 

[PeterF]

The datevalue function works without the time part, for both date and time, a function like this works.

Function convertcustomdate(customdate) As Date
    If Len(customdate & vbNullString) = 23 Then
            'only allow 23 char for "2006-07-07 00:00:00.000"
        convertcustomdate = DateValue(Left(customdate, 10)) + TimeValue(Mid(customdate, 12, 8))
            'only whole seconds as Timevalue doesn't recognize the .000 part
    End If
End Function

 

[raskew]

Hi -

It's not really necessary to specify left(...) or mid(...) Example from the debug (immediate) window:

x = "2006-07-07 10:15:20"
y = datevalue(x) + timevalue(x)
? y
7/7/06 10:15:20 AM  

to demonstrate that it's saved in date/time data format
? cdbl(y)
 38905.4273148148 

? cdate(38905.4273148148)
7/7/06 10:15:20 AM

Bob