Actually, I found the best way to work with dates in a SQLite table is to actually store the date as MM/DD/YYYY or DD/MM/YYYY depending on your continent. But also have another field in the table that has the following format : YYYYMMDD to sort with. The latter is created by manipulating the real date field. This way, you have a real date field to look at that makes sense like say: 3/12/2012 or 12/3/20122 and still be able to sort with the field that is YYYYMMDD in descending or ascending order.
I understand both of your situations. In my case, at the end of the day I create a text file from the SQLite table that I export to a master database at the PC level where it is appended to a SQL server or Access database table. A long stored in a SQL Server or Access database table will not help. This is for those of us who prefer a date as the data is exported.
I have started to code my app using yyyyMMdd format.
It will be a few weeks before I have the new one up and running.
I'm not concerned that the fields will show as yyyyMMdd - it's rare that I would read them raw like that. I have created a sub to convert that to a nice format :
Sub DOW(datein As Long) As String
'given the date in datein return the Day of week of that date
Dim ret As String
Dim i As Int
Select Case i
Sub DateStringLongFormat(date As String) As String
Dim day,mymonth,year As Int
Dim ret As String
year = date.SubString2(0,4)
mymonth = date.SubString2(4,6)
day = date.SubString2(6,8)
DateTime.DateFormat = "dd/MM/yyyy"
Dim l As Long
ret = day & "/" & mymonth & "/" & year
l = DateTime.DateParse(ret)
DateTime.DateFormat = "dd-MMM-yyyy"
ret = DOW(l) & " " & DateTime.Date(l)
I have also created this to convert dates like DateTime.Now to the internal yyyyMMdd format :
Sub DateLongToString(Date As Long) As String
DateTime.DateFormat = "yyyyMMdd"
With my issues I'm having with the DateTime object and how it tries to determine Timezone and DST by Offset alone (Which can't work) I decided to store Ticks in SQL Lite and convert it to text later when it goes to MySQL. I got the database code above (From lagore) to work to store the ticks and get Date / Time Text from the Database correctly...providing I don't forget the / 1000 again.
I have one area though where I need the user to provide Date and Time using both the Date and the Time Dialogs. This Mod code sounded perfect to split the two, but doesn't work. 1. Setting Timezone to 0 does nothing since Ticks are already in that timezone/offset and no conversions are being done to need that (Plus the issue I have with DateTime and being able to return the timezone to default not working [It tries to set timezone by offset instead of just setting the offset] I wouldn't be able to restore the conversion right even if used). 2. Really what this is doing is splitting the ticks at Midnight...Midnight of GMT/0, so when the timezone shifts get applied to the split values things shift wrong. Having the time portion by itself can't do much since depending on the date could have a DST applied. Since Ticks Start in Jan a DST shift would be applied in some areas. The Time Dialog also sets the date portion to Dec 31st which would also have DST applied (For some reason it sets the year to 0002 which also adds to the weirdness that needs stripped off...along with the ticks now being negative).
Anyone else having much luck splitting Date and Time better on the application side before saving to DB? And, also getting it to work with the Date and Time Dialogs? Only thing I can figure is shifting the ticks to match the current timezone then splitting, but I'd have to think through that one to be sure it wouldn't still have issues and it still wouldn't work right with the dialogs. I think if the Time Dialog kept the Date portion both in calculations and in what is returned it might work...does anyone actually use the funky 0002 year date portion that would break code?
DateTime.GetTimeZoneOffsetAt doesn't determine the timezone or DST. It just returns the time shift at the specific date. The time zone, which can have different values in different dates, is already known.