EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials SQL Tutorial SQLite Date

SQLite Date

SQLite Date

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

SQLite Date 1

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.

SQLite Date 2

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.

SQLite Date 3

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.

SQLite Date 4

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.

SQLite Date 5

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.

year

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.

Day

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.

day 1

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.

date

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 –

  1. SQL Injection Attack
  2. SQL BLOB
  3. SQL Delete View
  4. SQL Formatter
SPSS Training Program
41+ Hours of HD Videos
14 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
MICROSOFT AZURE
97+ Hours of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
HADOOP Training Program
170+ Hours of HD Videos
32 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
INFORMATICA Training
69+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
SQLite Tutorial
 12+ Hours of HD Videos
4 Courses
Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more