SQL Server Datetime object persistent reformatting issue in Excel -

i have annoying issue working sql server datetime objects in excel 2013. problem has been stated several times here in so, , know work around reformat datetime objects in excel doing this:

• right click cell
• choose format cells
• choose custom
• in type: input field enter yyyy-mm-dd hh:mm:ss.000

this works fine loathe having every time. there permanent work around aside creating macros? need maintain granularity of datetime object cannot use smalldatetime. using microsoft sql server management studio 2008 r2 on win7 machine.

thanks in advance.

-stelio k.

without code it's hard guess how data gets sql server excel. assume it's not through data connection, because excel wouldn't have issues displaying data dates directly.

what data connections?

excel doesn't support kind of formatting or useful designer matter, when working data connections only. functionality provided power query or pivottable designer. power query integrated in excel 2016 , available download excel 2010+.

why need format dates

excel doesn't preserve type information. string or number , display governed cell's format.

dates stored decimals using ole automation format - integral part number of dates since 1900-01-01 , fractional part time. why system.datetime has fromoadate , tooadate functions.

to create excel sheet dates, should set cell format @ same time generate cell.

how format cells

doing relatively if use open xml sdk or library epplus. following example creates excel sheet list of customers:

static void main(string[] args) {     var customers = new[]     {         new customer("a",datetime.now),         new customer("b",datetime.today.adddays(-1))     };     file.delete("customers.xlsx");     var newfile = new fileinfo(@"customers.xlsx");     using (excelpackage pck = new excelpackage(newfile))     {                         var ws = pck.workbook.worksheets.add("content");          // format string *is* affected user locale!         // , "mm-dd-yy"!         ws.column(2).style.numberformat.format = "m/d/yy h:mm";          //that's needs load data         ws.cells.loadfromcollection(customers,true);         pck.save();     } } 

the code uses loadfromcollection method load list of customers directly, without dealing cells. true means header generated.

there equivalent methods load data other source: loadfromdatatable, loadfromdatareader, loadfromtext csv data , loadfromarrays jagged object arrays.

the weird thing specifying m/d/yy h:mm or mm-dd-yy format uses user's locale formatting, not format! that's because these formats built-in excel , treated locale-dependent formats. in list of date formats shown asterisk, meaning affected user's locale.

the reason weirdness when excel moved xml-based xlsx format 10 years ago, preserved quirks of older xls format backward-compatibility reasons.

when epplus saves xlsx file detects them , stores reference built-in format id (22 , 14 respectively) instead of storing entire format string.

finding format ids

the list of standard format ids shown in numberingformat element documentation page of open xml standard. excel defined ids 0 (general) through 49.

epplus doesn't allow setting id directly. checks format string , maps formats 0-49 shown in getbfrombuildidfromformat method of excelnumberformat. in order id 22 need set format property "m/d/yy h:mm"

another trick check stylesheets of existing sheet. xlsx zipped package of xml files can opened decompression utility. styles stored in xl\styles.xml file.