me again - convert text to date

[nickpin]

Hi all, again,

I have a field in a table, text type, populated with dates/times, formatted like this: 2005-12-0116:15:00. None of them have spaces between the date and time, hence I can't use a cdate function to convert them. I need to be able to search on this field for a given day, so obviously some conversion needs to take place somewhere. Any ideas? I could try using a LEFT/RIGHT trim along with cdate, as I only really need the date rather than the time, but am unsure how this would work in a query?

I also need to account for null values in this field.

Any help much appreciated.

Nick

 

[nickpin]

I was a bit pre-emptive, and managed to do it. For anyone else with a similar issue the SQL code I used on the query is below:

INSERT INTO [convert] ( id, datetest )
SELECT Customer_Define.ID, CDate((Left(([customer_define].[calldatetime]),10))) AS Expr1
FROM Customer_Define
WHERE [customer_define].[calldatetime] is not null