--- title: "12 — Stored Procedures and Date/Time Functions" uid: stored-procedures-and-dates tags: ["sql", "roadmap:sql", "datepart", "timestamp", "encapsulation", "dateadd", "date", "stored-procedures", "functions"] excerpt: "Stored procedures encapsulate reusable multi-statement logic server-side; date/time functions are the specialized toolkit for the one data type with its own arithmetic." date: 2026-08-13T03:27:36+0000 source: https://www.aveshina.my.id/en/blog/stored-procedures-and-dates --- Two topics nested together on the roadmap looked unrelated until their connection surfaced. Writing them down revealed it: **stored procedures encapsulate reusable multi-statement logic server-side, and the date/time functions are the specialized toolkit for the one data type whose arithmetic is genuinely its own thing.** [1][2] The framing that clicked on the procedure side is *where the logic lives*. A query in application code has to travel over the wire, and complex multi-step logic means several round trips. A stored procedure parks that logic inside the database, callable by name, so a single call does the work of many statements. The framing on the date side is that dates aren't numbers — "add one month" crosses month boundaries and leap years, so SQL ships dedicated functions to do that arithmetic correctly. ```figure ``` ## Stored procedures vs functions Stored procedures and functions are both precompiled database objects that encapsulate SQL logic, but they serve different roles [1]: - **Stored procedures** encapsulate a sequence of operations — often data manipulation — and are invoked with CALL. They can run multiple statements, manage transactions, and don't necessarily return a value. - **Functions** compute and return a value, and can be used inside a SELECT (e.g., SELECT compute_tax(total) FROM orders). They're more restricted — typically no side effects — so they compose into queries where procedures don't. The shared payoff is encapsulation: the logic lives in the database, is callable by name, and every application that calls it gets the same behavior without re-implementing it. That also centralizes optimization — tune the procedure once, every caller benefits. ## Why put logic server-side The practical reasons to reach for a procedure [1]: - **Reduce network round trips.** A procedure that runs five statements does so in one call instead of five, cutting latency on chatty workflows. - **Code reuse.** Logic shared across services is written once in the database rather than copied into each. - **Security.** Applications can be granted execute permission on a procedure without direct table access, limiting what they can do to exactly what the procedure does. The trade-off is that logic in the database is harder to version, test, and instrument than logic in application code. I reach for procedures when the logic is tightly bound to the data (multi-step writes that must be atomic) and stay in application code when the logic is bound to the product (workflow rules, API shaping). ## DATE, TIME, TIMESTAMP — the temporal types Date and time have their own data types because they're not plain numbers [2][3][4]: - **DATE** — calendar date (2024-03-15), no time. Used for birthdates, event days [2]. - **TIME** — time of day (14:30:00), no date. - **TIMESTAMP** — a specific point in time, date plus time, often with sub-second precision. The standard created_at / updated_at columns [3]. TIMESTAMP is the workhorse for "when did this happen," and many engines auto-update a timestamp column when its row changes — the canonical updated_at behavior. ## DATEPART — pull a piece out DATEPART extracts a single component — year, month, day, hour, etc. — from a date or timestamp [5]: ``` SELECT DATEPART(year, order_placed_at) AS yr, DATEPART(month, order_placed_at) AS mon FROM orders; ``` This is how I bucket time-series data: group by DATEPART(year, ...) for annual summaries, by the month for monthly, by the weekday for "which day of the week gets the most orders." Most engines also offer a EXTRACT(field FROM col) spelling that does the same thing. ## DATEADD — shift a date in time DATEADD adds (or subtracts, with a negative count) an interval to a date — days, months, years [6]: ``` -- SQL Server spelling SELECT DATEADD(day, 30, order_placed_at) AS ships_by FROM orders; -- MySQL spelling SELECT DATE_ADD(order_placed_at, INTERVAL 30 DAY) AS ships_by FROM orders; ``` The reason this needs a dedicated function rather than + 30 is correct boundary handling: "add one month" to 2024-01-31 should yield 2024-02-29 (a leap year), not 2024-03-02. The function knows the calendar; arithmetic on a number doesn't. ## How I use this Two habits, one per side. For procedures: I put logic in the database when it's *about the data's integrity* (a multi-step write that must be atomic, a calculation every caller needs identically) and keep it in the application when it's *about the product* (workflow, presentation). For dates: I never store dates as strings or epoch numbers — I use the native DATE/TIMESTAMP types and do all arithmetic through DATEADD/DATEPART, because every "why are my dates off by one" bug I've met traces back to hand-rolled date math that ignored a leap year or a timezone. ## References [1] Shiksha, "Stored Procedure vs Function — What are the differences?," shiksha.com, 2024. [Online]. Available: [https://www.shiksha.com/online-courses/articles/stored-procedure-vs-function-what-are-the-differences/](https://www.shiksha.com/online-courses/articles/stored-procedure-vs-function-what-are-the-differences/) [2] YouTube, "Working with Dates," 2023. [Online]. Available: [https://www.youtube.com/watch?v=XyZ9HwXoR7o](https://www.youtube.com/watch?v=XyZ9HwXoR7o) [3] SQLShack, "Different SQL TimeStamp functions in SQL Server," sqlshack.com, 2024. [Online]. Available: [https://www.sqlshack.com/different-sql-timestamp-functions-in-sql-server/](https://www.sqlshack.com/different-sql-timestamp-functions-in-sql-server/) [4] PostgreSQL Tutorial, "PostgreSQL Data Types," postgresqltutorial.com, 2024. [Online]. Available: [https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-data-types/](https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-data-types/) [5] Hightouch, "SQL DATEPART," hightouch.com, 2024. [Online]. Available: [https://hightouch.com/sql-dictionary/sql-datepart](https://hightouch.com/sql-dictionary/sql-datepart) [6] MSSQLTips, "SQL DATEADD function," mssqltips.com, 2024. [Online]. Available: [https://www.mssqltips.com/sqlservertutorial/9380/sql-dateadd-function/](https://www.mssqltips.com/sqlservertutorial/9380/sql-dateadd-function/) ```quiz Q: What's the main difference between a stored procedure and a function? - A procedure encapsulates multi-statement operations and is called with CALL; a function returns a value and can be used inside SELECT - A procedure returns a value; a function does not correct: 0 explain: Procedures run sequences of operations (often with side effects) and are invoked via CALL. Functions compute and return a value that composes into queries, but are typically more restricted. Q: Which type would you use for a created_at column recording exactly when a row was inserted? - TIMESTAMP - DATE correct: 0 explain: TIMESTAMP stores a precise point in time (date + time, often sub-second). DATE holds only a calendar day with no time component. Q: Why does adding one month to a date need DATEADD rather than + 30? - Because "add one month" must respect month lengths and leap years, which plain integer arithmetic ignores - Because SQL cannot add integers to dates at all correct: 0 explain: DATEADD knows the calendar — it maps Jan 31 + 1 month to Feb 29 in a leap year. Plain arithmetic on a day count would overshoot. Q: DATEPART(year, '2024-03-15') returns… - 2024 - 3 correct: 0 explain: DATEPART extracts the named component. Asking for year returns 2024; asking for month returns 3. Q: A strong reason to put logic in a stored procedure rather than application code is… - the logic is tightly bound to data integrity and must run identically for every caller - the logic is about presentation and workflow correct: 0 explain: Procedures shine for data-bound logic (atomic multi-step writes, shared calculations). Product/workflow logic stays in application code, where it's easier to version and test. ```