SQLite query date format

[mindbox]

In what format should I pass a date to a SQLite query?

 

[mindbox]

After some digging around (SQLite Query Language: Date And Time Functions) the answer is YYYY-MM-DD.

 

[mc73]

It's a matter of taste, but also depends on what your aim is. For example, I always use the YYYYMMDD format. It's just a number, making sorting clear, and after all whenever I want I format the output as needed.

 

[Mahares]

DateTime.DateFormat = "yyyy-MM-dd"

After manipulating your queries in the above date format, you should return your date format to its original. For example, If you use month, day, year, then, you need to issue:

DateTime.DateFormat = "M/d/yyyy" 'example: 7/19/2012 starting format

Like mc73, I like to store a date as YYYYMMDD. It can be stored as a TEXT field or INTEGER in a SQLite table. You can do any WHERE clauses with it like between, >, < and also ORDER BY either DESC or ASC, etc.

 

[TyneBridges]

A related newbie question - how do we then display the number as a date after reading it? The DateFormat command doesn't work there because the compiler doesn't know that the value it has read is a date, so it just displays as a long number...

Thanks.

 

[Mahares]

Here is an example:

Dim MyDate, strDate As String 
strDate= "20120922"
MyDate=strDate.SubString2(4,6) &"/" & strDate.SubString(6) &"/" & strDate.SubString2(0,4)
Msgbox(MyDate,"")   'returns 09/22/2012
MyDate=strDate.SubString(6) &"/" & strDate.SubString2(4,6) &"/" &  strDate.SubString2(0,4)
Msgbox(MyDate,"")   'returns 22/09/2012

 

[TyneBridges]

Thanks. I guess it's the meaning of "lite" in SQLite that it handles dates in a way that is flexible, but also quite clumsy and error-prone because of the lack of variable typing.

 

[TyneBridges]

I'm still having lots of trouble with DateFormat and date arithmetic. The documentation suggests that the DateFormat command should only affect the way a date is displayed, not the way that the system stores it - or have I misunderstood this? I'm storing strings in my database in UK date order, but struggling with date manipulation.

The following code does not work as intended. CDateStr contains the expected value but I thought I would be able to add a week to this (regardless of DateFormat) using DateTime.Add(DateTime.Date(DateTime.Now), 0, 0, 7). However, the value of Main.CExpDate as it appears in my message box is completely garbled. Can anyone see what I'm doing wrong? Both CDateStr and CExpDate are defined as strings.

DateTime.DateFormat = "ddMMyyhhmm"
CDateStr = DateTime.Date(DateTime.Now)
Main.CExpDate = DateTime.Add(DateTime.Date(DateTime.Now), 0, 0, 7)
Msgbox("Date now stored as " & CDateStr & ". Expiry date: " & Main.CExpDate, Main.AppName)

:sign0013:

 

[Mahares]

To add 7 days to today see below:

Dim CExpDate As String
      DateTime.DateFormat = "dd/MM/yy hh:mm a" 
      CExpDate =DateTime.Date(DateTime.Add(DateTime.now,0,0,7))  'add 7 days to today
      Msgbox(CExpDate,"") 'returns 11/10/12  04:19 PM

If you set the: DateTime.DateFormat = "dd/MM/yy" it returns: 11/10/12

 

[TyneBridges]

Thanks. Following your example, I spotted that I had my syntax/bracketing the wrong way round.