Handling Dates Part 2
Part of the Fundamentals section of Coddy's SQL journey — lesson 32 of 72.
Dates cannot be added or subtracted like numbers. To perform calculations on dates, we must convert them to numbers using the JULIANDAY function. This function converts dates to the Julian day, a continuous count of days since January 1, 4713 BC, which is used by astronomers for time intervals and comparisons between different calendars.
To use it we will write:
SELECT JULIANDAY('2023-02-20 12:00:00')This will return 2459983.0 days
Challenge
EasyAvailable tables and columns:
<strong>events</strong>:<strong>id</strong>,<strong>start</strong>,<strong>end</strong>
Fetch the IDs of all events that last less than three days
Cheat sheet
To perform calculations on dates, convert them to numbers using the JULIANDAY function, which converts dates to Julian days:
SELECT JULIANDAY('2023-02-20 12:00:00')This returns the number of days since January 1, 4713 BC, allowing you to subtract dates to find time differences.
Try it yourself
Test yourselfThis lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in Fundamentals
4More Keywords
The IN keywordThe BETWEEN keywordThe LIKE keywordThe AS keywordRecap - Cellphone Models
2Conditions
Conditions BasicsThe AND keywordThe OR keywordThe NOT keywordMultiple Conditions CombinedParenthesisBooleans5Arithmetic Operations
Mathematical OperatorsMathematical ColumnsThe Modulo OperationThe ROUND() Function3Specific Return Format
Null valuesSort Results Part 1Sort Results Part 2Recap - Cyber Security FirmLimit number of recordsRecap - Vehicle Factory6Intro Challenges
Recap - Parliamentary ElectionRecap - Police Criminal ArrestRecap - Bar Beverage ContainerRecap - Engineer new columns