Convert DateTime to Date

[agmorgan]

I have data that has been returned from an SQL query that looks like this
2005-07-13 00:00:00.000
Unfortunately it is not recognised as a date so this doesn't work

Format("2005-07-13 00:00:00.000", "dd/mmm/yyyy")

Is there any short (1 line) way of converting it to 13/Jul/2005?

I can create my own function and use split, but I was wondering if there was a built in way.
Interestingly Excel has no problem formatting it with "dd/mmm/yyyy"

[leinad31]

The milliseconsd can't be handled. Removing it will use PC regional settings for parsing the date in the string. Still, it would be best to manually parse the string (your gonna modify the string anyway to remove the millisecond info) if you know its format and create the date using DateSerial() so you can avoid issues with regional settings.

[Keithuk]

Yes you can use Format

MsgBox Format(Now, "dd/mmm/yyyy")

Well it works for Now.

Now is a Date and Time.

[agmorgan]

Thanks Keith, but the point is that "2005-07-13 00:00:00.000" is not the same as Now and it isn't a Date.

I think I'll follow what leinad31 said and use DateSerial.

[Keithuk]

Yes but the reason your doesn't work is

Format("2005-07-13 00:00:00.000", "dd/mmm/yyyy")

Incorrect time value

Format("2005-07-13 13:05", "dd/mmm/yyyy")

This works.

[leinad31]

It will work now, but it may not work later.. it'll be vulnerable to user changes to short/long date format.

[CVMichael]

Just remove the milliseconds, and then you will have a valid date format:

Format(Split("2005-07-13 00:00:00.000", ".")(0), "dd/mmm/yyyy")

And since you don't use the time, you might as well, remove everything from the right of the space...

Format(Split("2005-07-13 00:00:00.000", " ")(0), "dd/mmm/yyyy")