Problem converting date stored as text to data/time format

[gddrew]

I have a problem converting text to a real date value so I can do some calculations. I have a query that brings in data from an external data source. It appears the data is stored in the external table in text format and looks like this:

20050902 15:40:41

I have tried CDate to convert the text to a date/time format, but no luck. Any ideas?

 

[AnotherHiggins]

Is this in Excel?

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

FYI

Why do Dates and Times seem to be so much trouble? faq68-5827

I'd edit the query; something like...

Select ...., DateSerial(Year(Left(DS,4),Mid(DS,5,2),Mid(DS,7,2))+ TimeSerial() As 'TheDate', ...

and you can figger out the time parsing for hour, minute and second.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[AnotherHiggins]

Nevermind, I guess the CDate should have tipped me off that it isn't Excel.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.