1. 

   nsepetys, Member

   Hello myDBR Representative,

   How are things going? I am noticing that when exporting dates (with date and time) excel does not recognize it as a date and time. I have tried adjusting the formatting but even with it set explicitly like it defaults to, when I see it recognized as a date and time in excel, it still treats it as a string. Is there a trick to getting excel to read it like a date and time right off the bat?

   Thanks,
   Noah
2. 

   myDBR Team, Key Master

   Noah,
   how does your query look like? What is the datatype of your date and time column?

   --
   myDBR Team
3. 

   nsepetys, Member

   Data type is DATETIME. Query looks like below:

   SELECT datesent FROM datatable;
4. 

   nsepetys, Member

   I've clearly underestimated the complexity of the problem. I added just a plain query like above and it was formatting clearly as a date/time in excel. So that works as intended.

   I believe the case statement that returns a user friendly format of the date/time is interfering. The case is like below:
   SELECT CASE WHEN @inExportFormat = 'xlsx' THEN CAST(datesent AS DATETIME) ELSE DATE_FORMAT(datesent , '%b %d, ''%y %r' ) END AS datesent FROM datatable;

   I have no idea why the implicit data type for the column is derived from the pre-evaluated statement as opposed to the query results themselves but that looks like what is happening. I guess I'll just create extra date/time columns that are shown only for their respective export types.
5. 

   myDBR Team, Key Master

   Noah,
   A SQL database (including MySQL) evaluates datatype for a result set column. If a column contains multiple datatypes (in case of CASE / IF statement, the datatype which can contain all data is selected. In your case the DATE_FORMAT prodcues a string, hence the column is evaluated as string.

   --
   myDBR Team
6. 

   nsepetys, Member

   ok. in any case if you want a human readable date time for html/pdf and a excel friendly format date time format the key is to create an entirely new column and use the dbr.hidecolumn command to hide what we don't want
7. 

   myDBR Team, Key Master

   Yes,
   if you want to have separate content on HTML and Excel, that would be the easiest way to do it.

   --
   myDBR Team

Reply

You must log in to post.