vb.net - Date Format not working with Polish hyphens -

i have code:

tempdate = exws.cells(exrow, mymatchedcolumns(2)).value 

which takes date cell in spreadsheet, have code:

duedate = format("dd/mm/yyyy", tempdate) 

which changes date whatever dd/mm/yyyy correct.

however, i've ran program on polish system (whos' regional default yyyy-mm-dd) when code runs;

temp date: yyyy-mm-dd

due date: dd-mm-yyyy

so format puts right way around it's not changing dashes slash.

naturally sql needs in format dd/mm/yyyy.

can format date better.

many thanks

there several common misconceptions in question.

first, , perhaps foremost, should turn on option strict. have implicit conversions going on.

tempdate = exws.cells(exrow, mymatchedcolumns(2)).value 

i have no idea how tempdate declared, pretty sure excel cell values object. tempdate @ best, date boxed object.

 duedate = format("dd/mm/yyyy", tempdate) 

that not "format date". converts datetime string in format.

dates not have format

net datetime types not have format. cannot apply format. there no need format date. msdn:

represents instant in time, typically expressed date , time of day.
... example, ticks value of 31241376000000000l represents date,
friday, january 01, 0100 12:00:00 midnight.

a datetime big number. value. today's date 635894496000000000l , datetime.now 635894883340193705l.

displaying values not make sense eric lippert¹, datetime type includes code 'express date , time of day'. aware of local culture single date display differently in poland vs uk vs italy no code or us. example:

dim mydt = datetime.now  console.writeline(mydt.tostring("yyyy-mm-dd")) console.writeline(mydt.tostring("mm/dd/yyyy")) console.writeline(mydt.tostring("d-m-yyyy")) ' use culture default:   console.writeline(mydt.tostring(new cultureinfo("pl-pl"))) console.writeline(mydt.tostring(new cultureinfo("fr-fr"))) 

2016-01-27
01/27/2016
27-1-2016
27/01/2016 11:27:13
2016-01-27 11:27:13

it same exact date , same exact value displays in format want, in culture want.

---

polish hyphens

as mentioned, format() returns string, not date. output correct because polish culture does not use slash:

dim cult = new cultureinfo("pl-pl") dim polishdtdisplayformat = cult.datetimeformat console.writeline(polishdtdisplayformat.dateseparator) ' prints '-' 

unlike cultures such or uk can use either, slash not defined polish. format substitutes legal date separator defined culture illegal character specified. datetime same:

' try override because think know better: console.writeline(mydt.tostring("yyyy/mm/dd", new cultureinfo("pl-pl"))) 

2016-01-27

this thing! prevents having culture's dateseperator , concoct format string in code. powerful datetime type includes methods such toshortdatestring() display date in legal, local order ("m/d/yyyy" vs "yyyy-m-d").

use dotnetfiddle examine legal formats culture.

---

naturally sql needs in format dd/mm/yyyy

sorry, not.

using (mysql?) net db provider objects, , column defined datetime, need save net datetime variable database:

 using dbcon = new mysqlconnection(connstr)         using cmd new mysqlcommand(sql, dbcon)             dbcon.open()             cmd.parameters.add("@p1", mysqldbtype.datetime).value = mydt             rows = cmd.executenonquery()         end using   end using    

the same true oledb/access , sql server. know do. net db provider objects pretty useless unable use net datetime type underlying database.

red herrings
access includes "format" property date columns. not specify save format, how want access display in ide , users. if wizard vba form , map column control, uses format display.

in cases, format how date displayed (output) not stored.

similarly, mysql mentions required "yyyy-mm-dd" format. careful reading of documentation reveals talking date data text. instance, format use when using cli or workbench ui. result does work:

cmd.parameters.add("@p1", mysqldbtype.datetime).value = mydt.tostring("yyyy-mm-dd") 

...but not needed. before converts datetime to string (hence clever name) contains date data. db provider objects going have parse date in order actual value.

it can seem required date format because other formats may fail. because chose convert date text. work fine:

cmd.parameters.add("@p1", mysqldbtype.datetime).value = mydt 

summary

• use datetime variables date data
• store date date date fields in database
• use .tostring(...) specify how display date user
• turn on option strict prevent accidental type conversions
• dates not have intrinsic format

---

¹ probably