Date datatype converts date => datetime

[scottsteinbeck]

Reproducible use case

  data = QueryNew( "Date", "VarChar", [ [  "2020-08-24" ] ] );
  datatypes = { date: [ "Date" ] };
  spreadsheet = New spreadsheet();
  workbook = spreadsheet.new();
  spreadsheet.addRows( workbook=workbook, data=data, datatypes=datatypes );

You can see that before it was a date with no time. after setting the datatype, the date now has a time of 7:00:00 AM

[cfsimplicity]

Hi Scott

Running your code I get different results. The value is showing as a date with no time in both Excel...

...and Libre Office

Dates are actually stored as numbers in excel, whole ones for date only values, with fractions added for times.

To check what is actually being recorded in your workbook object, try adding the following code to your test case:

WriteDump( spreadsheet.getCellType( workbook, 1, 1 ) );
WriteDump( spreadsheet.getCellValue( workbook, 1, 1 ) );
spreadsheet.formatCell( workbook, { dataformat: "0.00000" }, 1, 1 );
WriteDump( spreadsheet.getCellValue( workbook, 1, 1 ) );

This is what I'm seeing, i.e. definitely a whole number, no time fractions:

What do you get?

[scottsteinbeck]

I will say that i updated my timezone to reflect PST, I wonder if this is causing the issue. Though with a static date it shouldnt.

[scottsteinbeck]

Confirmed! Upon testing resetting the timezone inside lucee admin to the default (same as server), it has corrected the numeric value of the date

[scottsteinbeck]

I would assume this is a lucee issue and not a library issue correct?

[cfsimplicity]

I'm not sure it's necessarily either, although it's complicated and adding timezones into the mix makes it more so! I'm not an expert in this, but here's my current partial understanding.

The cell types the library lets you specify are string, numeric, date, time, boolean and auto.

date and time both actually translate to a datetime value, since that's all CF/POI/Excel understand.

The library will however try to identify "date only" and "time only" values and set the cell format to only display those portions of the datetime value in the cell. But the underlying datetime value is still stored in Excel as a number as explained above.

I don't fully understand the effect of changing the Lucee timezone, but I believe that POI/Excel uses the local machine timezone, so if there is a mismatch that will affect what POI stores.

Apparently POI does provide a way of specifying the timezone. I'll have a look at whether that could be a potential solution.

[cfsimplicity]

Forcing POI to match the Lucee timezone when setting date values does seem to fix the issue according to my tests.

Please check with the latest develop branch, Scott, and if it's still not working for you just re-open the ticket.