SQLite date type string converted to date type

Because SQLite is a weak reference, use a field to force it to date before using DateTime. Or the most primitive strftime.

SELECT distinct ID from testtable where datetime (availdate) between datetime (' 2015-01-12 04:00 ') and datetime (' 2015-01-13 00:00 ');

SQLite Date-time function

SQLite supports the following five date-time functions:

1. Date (timestring, modifier, modifier, ... ) )
2. Time (timestring, modifier, modifier, ... )
3. datetime (timestring, modifier, modifier, ... ) )
4. julianday (timestring, modifier, modifier, ... )
5. strftime (format, timestring, modifier, modifier, ... ) )

All of the five functions are arguments with time character channeling (timestring), while 0 or more modifiers can be received after the time character channeling. In addition, the strftime () function is somewhat different from its four functions, and it can also receive a character channeling format (using the character channeling format with the printf function in the C function).

SQLite's datetime function uses a subset of the ISO-8601 date-time format specification. The date () function returns the format: the Yyyy-mm-dd,time () function returns the time format: the Hh:mm:ss,datetime () function returns a format of "Yyyy-mm-dd HH:MM:SS". Julianday () returns the Julian day-the number of days from the noon of November 24, 4714 BC (GMT) to the current date. The strftime () function formats the user's time input (the second parameter) according to the user-specified format character channeling (the first parameter). The composition of the format character channeling is basically similar to the formatting parameters in the C function, but the meaning of the expression is not the same. Mainly as follows:
%d Day of month:00
%f fractional Seconds:ss. Sss
%H hour:00-24
%j Day of year:001-366
%J Julian Day Number
%m month:01-12
%M minute:00-59
%s seconds since 1970-01-01
%s seconds:00-59
%w Day's week 0-6 with sunday==0
%W Week of Year:00-53
%Y year:0000-9999
%% %

In addition, all other DateTime functions can be expressed in the form of a strftime () function, as follows:
Function equivalent Strftime ()
Date (...) Strftime ('%y-%m-%d ', ...)
Time (...) Strftime ('%h:%m:%s ', ...)
DateTime (...) Strftime ('%y-%m-%d%h:%m:%s ', ...)
Julianday (...) Strftime ('%J ', ...)

However, other functions, other than strftime, are provided entirely from convenience and performance considerations.

Time character channeling (temporal String)

The time character channeling can be any one of the following forms:

1. Yyyy-mm-dd
2. YYYY-MM-DD hh:mm
3. YYYY-MM-DD HH:MM:SS
4. Yyyy-mm-dd HH:MM:SS. Sss
5. YYYY-MM-DD T hh:mm
6. YYYY-MM-DD T HH:MM:SS
7. YYYY-MM-DD T HH:MM:SS.SSS
8. hh:mm
9. HH:MM:SS
10. HH:MM:SS. Sss
11. Now
12. Dddddddddd

In the 5th to 7th, T is used to separate the DateTime (refer to ISO-8601). 8 to 10 specifies the time format, because there is no date input, the default date for these several formats is 2000-01-01. In 11th, ' now ' will be converted to the current date and time.

Modifier (Modifiers)

The time string background can add 0 or more modifiers to assist in changing the time character channeling. Each modifier is a conversion to its left value, and when there are multiple modifiers, it takes effect from left to right. The available modifiers are:

1. NNN days
2. NNN hours
3. NNN minutes
4. NNN. NNNN seconds
5. NNN months
6. NNN years
7. Start of month
8. Start of year
9. Start of day
10. Weekday N
11. Unixepoch
12. LocalTime
13. Utc

The first 6 modifiers are the increase or decrease in the time result after the time character channeling and its preceding modifiers are processed. For example, for the time in YYYY-MM-DD format, when the "±nnn months" modifier is used, the corresponding number of months is increased/decreased for MM.

Example

Current date
SELECT date (' Now ');

Last day of the month
SELECT date (' Now ', ' start of Month ', ' +1 month ', '-1 day ');

Convert a Unix timestamp to a time date format
SELECT datetime (1092941466, ' Unixepoch ');

Convert UNIX timestamps to local time
SELECT datetime (1092941466, ' Unixepoch ', ' localtime ');

The UNIX timestamp format for the current date
SELECT strftime ('%s ', ' Now ');

Calculates the date difference between the day and the U.S. Independence Day (in days)
SELECT julianday (' Now ') –julianday (' 1776-07-04′);

Calculates the time difference (in seconds) from any period to the current time
SELECT strftime ('%s ', ' Now ') –strftime ('%s ', ' 2004-01-01 02:34:56′);

The date is determined in October of this year's article One Tuesday
SELECT date (' Now ', ' start of Year ', ' +9 months ', ' weekday 2′);

Original: http://blog.romebuilder.com/?p=96

SQLite date type string converted to date type