Strip time off a date?

[Ricky D]

I would like to store certain date fields in my database as the date only with time set to 0.

How do I do this?

regards, Ricky

 

[Erel]

Something like:

DateTime.SetTimeZone(0)
Dim date, time As Long
date = DateTime.Now
time = date Mod DateTime.TicksPerDay
date = date - time

 

[Ricky D]

Thanks again Erel.

I'll code it into a sub to return the cleansed date as a long

regards, Ricky

 

[Ricky D]

here is my sub

Sub StripTimeOffDate(date As Long) As Long
   DateTime.SetTimeZone(0)
   Dim time As Long
   time = date Mod DateTime.TicksPerDay
   
   Return date - time
End Sub

regards, Ricky

 

[Mahares]

Here is an example just in case you want to store your date and time as a text (not long) in 2 separate fields in the SQLite database table:

Dim DateNow, TimeNow as Edittext
'The variables do not have to be text boxes
DateNow.Text= DateTime.Date(DateTime.Now)
TimeNow.Text = DateTime.Time(DateTime.Now)

 

[Ricky D]

@Mahares thanks

I store all my dates in my dbs as long for sorting purposes as well as selecting records between 2 dates

If I wanted to sort dates that were text I'd need to store them as
YYYYMMDD format wouldn't I?

As I've already created a large app for my work (I'm a taxi driver) and written the code for all dates with the long storage I'll stick with it.

Mind you it's easier to read raw sql queries on the device using text storage.

When I have time I'll trial it out actually.

regards, Ricky

 

[Mahares]

Ricky:
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.

 

[Ricky D]

What I intend to do is store only YYYYMMdd dates and write a sub to convert it to a ddd, dd-MMM-YYYY format like Tue, 13-Mar-2012 for readability purposes. I'm the only one using the apps I'm writing.

 

[lagore]

I agree with Ricky, I find it easier to store the date/time as a long it can be even used as the primary key and then if you want to display the date I use

date(MyDateTimeLong/ 1000, 'unixepoch')

and for time I use

 strftime('%H:%M', MyDateTimeLong/ 1000, 'unixepoch')

in the Select Query.

 

[Mahares]

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.

 

[Ricky D]

Fair enough Mahares.

I do understand.

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
   i=DateTime.GetDayOfWeek(datein)
   ret=""
   
   Select Case i
      Case 1
         ret="Sun"
         
      Case 2
         ret="Mon"
         
      Case 3
         ret="Tue"
         
      Case 4
         ret="Wed"
         
      Case 5
         ret="Thu"
         
      Case 6
         ret="Fri"
         
      Case 7
         ret="Sat"
   End Select
   
   Return ret
End Sub

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)
   
   Return ret
End Sub

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"
   
   Return DateTime.Date(Date)
End Sub

So now it's time to copy each activity and test

regards, Ricky

 

[Roger Garstang]

Something like:

DateTime.SetTimeZone(0)
Dim date, time As Long
date = DateTime.Now
time = date Mod DateTime.TicksPerDay
date = date - time

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?

 

[Erel]

See my answer in your thread. DateTime correctly calculates the time zone and the DST.

The issue you experienced was related to how TimeDialog calculated the ticks.

how it tries to determine Timezone and DST by Offset alone (Which can't work)

Why???
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.

 

[Roger Daley]

Something like:

DateTime.SetTimeZone(0)
Dim date, time As Long
date = DateTime.Now
time = date Mod DateTime.TicksPerDay
date = date - time

Erel,

Just for clarity, are there any issues that require a "reset" back to the local time zone?
It all appears to work OK but...

Roger

 

[Erel]

This is an old thread. Date / time arithmetics are much more complicated than it may first seem.

Please start a new thread and explain what you are trying to implement.