Updated June 8, 2023
Definition of SQLite Date Function
SQlite provides the different types of functions to the users, in which that date () is one of the functions that return the current data or future date that depend on the user requirement, by using the date () function we can set the date. Basically date () function fetches the time as well as zero or different modifiers as an argument and it prints the date in YYYY-MM-DD format. Normally date SQLite does not support built-in date storage class, instead built-in class SQLite uses the other storage class such as real or integer or text for storing the data.
Syntax:
date(specified time string, specified modifier 1, modifier 2,……..modifier N)
Explanation
In the above syntax, we use the date () function, here we use modifiers that are useful to apply the date transformation, and basically, modifiers are implemented from left to right so we can improve their orders.
How date function works in SQLite?
Now let’s see how date () function works in SQLite as follows.
Basically, date () function uses time as an argument, and time values uses zero or different modifiers.
The date () function utilizes a subset of IS0-8601 for date designs. The date () function returns the date in this organization: YYYY-MM-DD. The datetime () function returns “YYYY-MM-DD HH:MM: SS”. The strftime () routine returns the date arranged by the organization string indicated as the principal contention. The arrangement string upholds the most well-known replacements found in the strftime () function from the standard C library in addition to two new replacements, %f and %J. Coming up next is a finished rundown of substantial strftime () replacements:
- %d it is used for a specified day of the month.
- %j is used for a specified day of the year.
- %m is used for a specified month.
- %w it is used for a specified day of the week.
- %W it is used for a specified week of the year.
- %Y it is used for years.
Modifiers
The time value can be trailed by nothing or more modifiers that modify the date and additionally time. Every modifier is a change that is applied to the time value to one side. Modifiers are applied from left to right; request is significant. The accessible modifiers are as per the following.
1. NNN days
2. NNN hours
3. NNN minutes
4. NNN.NNNN seconds
5. NNN months
6. NNN years
7. Beginning of month
8. Beginning of year
9. Beginning of day
The initial six modifiers (1 through 6) essentially add the predetermined measure of time to the date and time indicated by the contentions to one side. The ‘s’ character toward the finish of the modifier names is discretionary. Note that “±NNN months” works by delivering the first date into the YYYY-MM-DD design, adding the ±NNN to the MM month esteem, at that point normalizing the outcome. Subsequently, for instance, the information 2001-03-31 changed by ‘multi month’ at first yields 2001-04-31, however, April just has 30 days so the date is standardized to 2001-05-01. A comparable impact happens when the first date is February 29 of a leap year and the modifier is ±N years where N is certifiably not a numerous of four.
The “beginning of” modifiers (7 through 9) move the date in reverse to the start of the current month, year or day.
Examples
Now let’s see the different examples of the date () function as follows.
select date(“now”);
Explanation:
In the above example, we use a select statement with date () function and here we use a modifier, which fetches the current date of the system. The end result of the above statement is shown in the below screenshot.
select date(“now”, “start of month”, “+2 month”, “-2 day”);
Explanation
In the above example, we use a select statement with a date () function and with different modifiers. Suppose we need to know the second last day of next month at that time we can use the above statement. In the above example first, the start of the month is work with the current date specified by now modifier, +2 is used to the second day of the current month that returns the same day from the next month, -2 is used to determine the second day of the next month. The end result of the above statement is shown in the below screenshot.
select date(“now”, “localtime”);
Explanation
See in the above example we use select statement with date () function as shown in the above statement, here we use two modifiers such as now and local time. It returns the current date of the system. The end result of the above statement is shown in the below screenshot.
Suppose users need to subtract the day from the current date at that time we can use the below statement as follows.
select date(“2021-03-20”, “-1 day”);
Explanation
In the above example we use select statement with date () function, here we use two modifiers such as the current date and after that we -1 day to know the last day. The end result of the above statement is shown below screenshot.
Similarly, we can subtract a month from the date by using the following statement as follows.
select date(“2021-03-20”, “-1 month”);
Explanation
In the above example we use select statement with date () function, here we use two modifiers such as the current date and after that we -1 month to know last month. The end result of the above statement as shown the below screenshot.
The same way we can subtract a year by using the following statement as follows.
select date(“2021-03-20”, “-1 year”);
Explanation
The end result of the above statement is shown in the below screenshot.
Now let’s see if we can add a day to date as follows.
select date(“2021-03-20”, “+1 day”);
The end result of the above statement is shown in the below screenshot.
Now add month to the date as follows.
select date(“2021-03-20”, “+1 month”);
The end result of the above statement is shown below screenshot.
Now add the year to the date as follows.
select date(“2021-03-20”, “+1 year”);
The end result of the above statement is shown the below screenshot.
Conclusion
We hope from this article you have understood about the SQLite date () function. From the above article, we have learned the basic syntax of date () function and we also see different examples of date () function. From this article, we learned how and when we use the SQLite date () function.
Recommended Articles
This is a guide to SQLite Date. Here we discuss the definition, syntax, How the date function works in SQLite? and examples. You may also have a look at the following articles to learn more –
