# Adding a date parameter

**URL:** <https://meta.discourse.org/t/adding-a-date-parameter/276158>\
**Category:** Data & reporting\
**Tags:** sql-tutorial\
**Created:** [August 22, 2023, 9:18am UTC](https://meta.discourse.org/t/adding-a-date-parameter/276158 "2023-08-22T09:18:23Z")\
**Posts on this page:** 1\
**Showing post:** 1

<div class="post-metadata">

**Author:** ![JammyDodger](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jammydodger/32/254611_2.png) [@JammyDodger](https://meta.discourse.org/u/JammyDodger)\
**Post date:** [August 22, 2023, 9:18am UTC](https://meta.discourse.org/t/adding-a-date-parameter/276158/1 "2023-08-22T09:18:23Z")

</div>

Often you will want to pull out data for a specific time period. You can do this by hard-coding in your time period into the query itself, but to make your report more future-proof you can add a date parameter:

> This query is designed to count the total number of posts in each category (excluding deleted posts and topics, private messages, and topics without a category) that were created within a specific date range, and then order the categories by the total number of posts in descending order.

```sql
-- [params]
-- date :start_date -- This is a parameter for the start date of the range
-- date :end_date -- This is a parameter for the end date of the range

SELECT
   t.category_id,
   COUNT(p.id) AS "Total Posts" -- Count the number of posts in each category
FROM posts p 
  JOIN topics t ON t.id = p.topic_id 
WHERE p.created_at::date BETWEEN :start_date AND :end_date -- Filter posts that were created between the start date and the end date (::date casts the created_at timestamp as a date)
  AND t.deleted_at IS NULL 
  AND p.deleted_at IS NULL 
  AND t.archetype <> 'private message' 
  AND p.user_id > 0
  AND t.category_id IS NOT NULL 
GROUP BY 1 
ORDER BY 2 DESC 

```

Using the parameter style `date` rather than `string` makes entering the parameter value much more intuitive, especially when shared with others.

Another notable tip for this type of query is to cast the `created_at` as a date. As the value is stored in the database as a timestamp, if you don’t cast it to a date the query won’t pick up the results from the date of the `:end_date` itself.

You can check which values are stored as timestamps using the Explorer tree:

 ![explorer tree - value type](https://global.discourse-cdn.com/meta/original/4X/e/6/9/e693c9dcd670adcc4a4843e062cfc3ca9f4c4640.png)

This query is mainly an example to demonstrate the principles. Hopefully you find it useful, but if you have any questions please ask them below. 🙂

---

_[View the full topic](https://meta.discourse.org/t/adding-a-date-parameter/276158)._