Date Formatting

[Fifer]

Can anyone help me please!!! I receive a csv file in which I need to import it into a table. It contains a date feild which looks like this '2007-06-15 12:17:54.953000000' and I would like it to look like this 'ddmmyyyy 00:00:00'. I have tried importing the data then changing the imported field to date and time format but it deletes it, and I have also tried to format it into a query using Format() but this is unsuccessful also. I have a work around which is to change the CSV file into excel, format it how I want it and then import, but ideally I would like it to skip that part.

Any Suggestions welcome

 

[Brianwarnock]

DateSerial(Left([yourdate],4),Mid([yourdate],6,2),Mid([yourdate],9,2)) will convert yourdate field, the time will default to 00:00:00

Brian

 

[Fifer]

Thanks this is a step in the right direction, however I would prefer the time field also. Is This possible??

 

[Brianwarnock]

DateSerial(Left([yourdate],4),Mid([yourdate],6,2),Mid([yourdate],9,2)) + timeValue(mid(yourdate,12,8))

I thought you wanted it 00 as per your post.
Note that there is also a TimeSerial function, these functions are found in VBA help.

Brian

 

[Brianwarnock]

A thought struck me if you use TimeValue(mid(yourdate,12,11) you will pick up the 1st two places of decimals and thus in your example have 55 not 54 seconds returned, is that what you would want?

Brian

 

[Fifer]

You are an absolute starxx Saved me a lot of time and head scratching. Thank you

Yes the time rounding up isn't too much of an issue. but thats fab. Thank you again