1

I'm using Thread.CurrentThread.CurrentCulture to display culture-sensitive data on the UI. It works well, as I just set it in one place and it affects entire application. The problem right now is when I'm saving the data to sqlite database, it's converting the datetime format before saving it, which causes problem when I'm parsing it the from the db afterward.

specifically, it's Norwegian datetime format which uses . instead of /

e.g 02/21/2017 becomes 02.21.2017

I can solve this problem by setting the culture to invariant before I do any update or insert then revert back the culture when it's done. This is not ideal because I'll have to manually change every single insert.

does sqlite provide any setting that allows me to force it to use invariant culture or is there any other way to set culture which avoid this issue?

edit. This is for Xamarin.iOS, I'm trying to avoid any major code change as the project is shared across another platform(windows wpf, which doesn't have the problem)

sponge
  • 31
  • 4
  • I don't know about Xamarin, but as a rule we want to use a `locale` of `en_US_POSIX` (and GMT/UTC/Zulu timezone). And, no, there isn't a SQLite setting that does that for you. You have to do that yourself. – Rob May 11 '17 at 16:44
  • 1
    Also, I would encourage you to use one of SQLite's preferred "time strings" [shown here](http://sqlite.org/lang_datefunc.html). If you store your dates in `MM/dd/yyyy` format, you can't use `where` clauses and you cannot sort. In the absence of SQLite native date type, if you choose to store it as a string, you really want `yyyy-MM-dd` format, with year first. Or use numeric format, e.g. `unixepoch`/[`timeIntervalSince1970`](https://developer.apple.com/reference/foundation/date/1780353-init). – Rob May 11 '17 at 16:54
  • 1
    I don't know about Xamarin either, but to add to Rob's comment, if you already have dates in the database formatted MM.DD.YYYY, you can convert them with: UPDATE X SET DATE=SUBSTR(DATE,7)||'-'||SUBSTR(DATE,4,2)||'-'||SUBSTR(DATE,1,2); If your application is receiving data in the form MM.DD.YYYY and you wish to insert into the the database, then use INSERT INTO X(DATE) VALUES = SUBSTR(date,7)||'-'||SUBSTR(date,4,2)||'-'||SUBSTR(date,1,2); – varro May 11 '17 at 22:30

0 Answers0