# 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:** 3
**Page:** 1
### Author:  [@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")
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:
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. ๐
---
### Author:  [@Jagster](https://meta.discourse.org/u/Jagster)
#### Post date: [August 22, 2023, 1:06pm UTC](https://meta.discourse.org/t/adding-a-date-parameter/276158/2 "2023-08-22T13:06:23Z")
```plaintext
PG::SyntaxError: ERROR: syntax error at or near ":"
LINE 16: WHERE p.created_at::date BETWEEN :start_date AND :end_date -โฆ
```
๐ซ
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [August 22, 2023, 1:10pm UTC](https://meta.discourse.org/t/adding-a-date-parameter/276158/3 "2023-08-22T13:10:09Z")
Ah yes, it doesnโt like those inline comments next the parameters.
```sql
-- [params]
-- date :start_date
-- date :end_date
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
```
This one seems to work better. ๐ (You may need to refresh the page the first time to pick up the parameter input boxes)
Thanks for helping me de-bug the guides. ๐ ๐