Open in Excel (7.1.0.118)

JimF

When opening a result grid that contains datetime fields, the datetime values appear to be exported as strings. that is, if I have a datetime value of '2014-07-08 00:00:00.000', it still says '2014-07-08 00:00:00.000' in Excel even if I apply a date format to it like 'mm/dd/yyyy'. I have to go to each value and click into it and then just press enter for Excel to reevaluate the value and make it a date time.

I don't know how much control you have with the data format, but if you can ensure that it is a Excel date/time field, that would be great. A compatible Excel format specifier would be "yyyy-mm-dd hh:mm:ss.000", otherwise it just shows the time and not the date.

Better yet would be to let me specify a default date format as I personally hardly ever use milliseconds in Excel (personally, I would use "yyyy-mm-dd hh:mm:ss" or just "yyyy-mm-dd hh:mm", in most cases). But, if you can at least ensure that all datetimes are not text values, then I can at least set the format to what I want without touching every single cell.

Ali D

Hi Jim

Glad to hear you're using the export feature already. You're right, the SQL datetimes are getting exported into Excel as strings.

It is possible to format these cells in bulk without having to click inside each one:
1. In Excel, set that column's data type to "Date" (highlight, right click > Format Cells... > select Date in the Category list).
2. With these cells still highlighted, go to DATA (in the ribbon) > Text to Columns > Finish.

Configuring your preferred date time format and then exporting to Excel as that data type is a reasonably large piece of work. If you'd like to see this functionality in SQL Prompt then please submit a UserVoice request here. This will help us gauge interest and prioritise the work.

Thanks
Ali

JimF

Ali wrote:

Hi Jim

Glad to hear you're using the export feature already. You're right, the SQL datetimes are getting exported into Excel as strings.

It is possible to format these cells in bulk without having to click inside each one:
1. In Excel, set that column's data type to "Date" (highlight, right click > Format Cells... > select Date in the Category list).
2. With these cells still highlighted, go to DATA (in the ribbon) > Text to Columns > Finish.

Configuring your preferred date time format and then exporting to Excel as that data type is a reasonably large piece of work. If you'd like to see this functionality in SQL Prompt then please submit a UserVoice request here. This will help us gauge interest and prioritise the work.

Thanks
Ali

Thanks for the reply and info on how to convert the data back to a date.

Sure, I understand that configuring a date time format to my preference will be a bit of work and I will look at posting something at UserVoice on that. But, what about at least formatting the field as "yyyy-mm-dd hh:mm:ss.000" statically (in your code) instead of as text so that it is at least a date time field in Excel and easier for users to change the formatting instead of doing a Text to Columns routine first.

Thanks,
Jim

Ali D

Hi Jim

I spent a few hours looking into exporting datetimes directly into Excel. Unfortunately, Excel only has separate date and time column types with no native analogue of a SQL datetime object. Setting a column's type to a string or number is very simple, creating a custom cell format to display a datetime is more complex, especially when programmatically generating the Excel file from code.

If you create a UserVoice request for this alongside your original request, we will look at this again if it a lot of people are having the same problem.

Thanks
Ali

JimF

May I ask by what mechanism you are exporting to Excel? Are you creating a file directly or using an Office Interop? I wouldn't mind doing a little poking around if I knew your base technique for doing the Export.

Ali D

Thanks for all your help with this - it's now fixed in the latest beta.