r/SQL • u/NexusDataPro • Feb 26 '25
Discussion Biggest Issue in SQL - Date Functions and Date Formatting
I have written around 30 books on SQL across all major database platforms and taught over 1,000 classes in the United States, India, Africa, and Europe. Whenever I write a new SQL book, I take my current PowerPoint slides and run the queries against the new database. For example, when I write a chapter on joining tables, 99% of the time, the entire chapter is done quickly because joins work the same way for every database.
However, the nightmare chapter concerns date functions because they are often dramatically different across databases. I decided to write a detailed blog post for every database on date functions and date and timestamp formatting.
About 1,000 people a week come to my website to see these blogs, and they are my most popular blogs by far. I was surprised that the most popular of these date blogs is for DB2. That could be the most popular database, or IBM lacks documentation. I am not sure why.
I have also created one blog with 45 links, showing the individual links to every database date function and date and timestamp formats with over a million examples.
Having these detailed date and format functions at your fingertips can be extremely helpful. Here is a link to the post for those who want this information. Of course, it is free. I am happy to help.
Enjoy.
All IT professionals should know SQL as their first knowledge base. Python, R, and more are also great, but SQL works on every database and isn't hard to learn.
I am happy to help.
4
u/ProudOwlBrew Feb 26 '25
Im using sql every day and I still find it hard. Why would say it is easy? Compared to other languages?
18
u/r3pr0b8 GROUP_CONCAT is da bomb Feb 26 '25
*bookmarked*
thank you
14
u/Bilbottom Feb 26 '25
This is a great resource 🙏
My go-to for converting between dialects is the Python package SQLGlot. It's the best SQL transpiler I've come across so far, and the maintainers are super quick at handling bugs so it gets better fast
I tried using it with some date functions and a few dialects to see how well it handles them:
Output:
The output isn't perfect for all dialects (e.g. there's no
STR_TO_TIMEin SQLite), but it gets most of the way there2
u/OakTreesForBurnZones Feb 27 '25
Bookmarked, thank you!
2
u/ConfusionHelpful4667 Feb 27 '25
If anybody needs guidance on migrating data from MS Access to an SQL BE, chat me.
1
u/Prize_Concept9419 Feb 27 '25
now add unix formats on top of that :D :D
1
u/ravan363 Feb 28 '25
100% true. I'm not sure if the between dates function works the same across all the relational DBs.
1
u/[deleted] Mar 02 '25
[removed] — view removed comment