# Calculate date parameters to use in program

**URL:** <https://forums.toadworld.com/t/calculate-date-parameters-to-use-in-program/33122>\
**Category:** Toad Data Point & Toad Intelligence Central\
**Created:** [April 14, 2015, 6:02pm UTC](https://forums.toadworld.com/t/calculate-date-parameters-to-use-in-program/33122 "2015-04-14T18:02:48Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![sharon.esler](https://avatars.discourse-cdn.com/v4/letter/s/f08c70/32.png) [@sharon.esler](https://forums.toadworld.com/u/sharon.esler)\
**Post date:** [April 14, 2015, 6:02pm UTC](https://forums.toadworld.com/t/calculate-date-parameters-to-use-in-program/33122/1 "2015-04-14T18:02:48Z")

</div>

Hi have sql program that I run each week which includes this statement:

where cast(a.createddate as **date** ) \>= cast(‘2015-04-05’ as **date** ) and cast(a.createddate as **date** ) \<= cast(‘2015-04-11’ as **date** )

What I would like to do is calculate the start and end dates automatically without any hardcoding of dates.

This program is run every Monday for the previous week which commences Sunday and ends Saturday

So if I was to run it on Monday 13 April my start date would be previous Sunday minus 1 week - ‘2015-04-05’ and my end date would be previous Saturday - ‘2015-04-11’

Much appreciated if anyone can help me with the syntax to achieve this.

Sharon

---

<div class="post-metadata">

**Author:** ![GregDavis11009](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/gregdavis11009/32/122_2.png) [@GregDavis11009](https://forums.toadworld.com/u/GregDavis11009)\
**Post date:** [April 15, 2015, 8:37am UTC](https://forums.toadworld.com/t/calculate-date-parameters-to-use-in-program/33122/2 "2015-04-15T08:37:17Z")

</div>

Date functions vary by platform. Which one are you connecting to Oracle, Sql Server, Sybase, etc.? Relative date functions in Oracle just use sysdate - 7 and sysdate - 1. SQL Server use dateAdd(d, -7, getdate()) and dateAdd(d, -1, getdate()). Also instead of \>= use where cast(a.createddate as date) between dateAdd(d, -7, getdate()) and dateAdd(d, -1, getdate()).

---

<div class="post-metadata">

**Author:** ![sharon.esler](https://avatars.discourse-cdn.com/v4/letter/s/f08c70/32.png) [@sharon.esler](https://forums.toadworld.com/u/sharon.esler)\
**Post date:** [April 15, 2015, 3:15pm UTC](https://forums.toadworld.com/t/calculate-date-parameters-to-use-in-program/33122/3 "2015-04-15T15:15:44Z")

</div>

Thanks Greg for your response.

I am connecting to HP Vertica.

In theory I see why the above should work, however that isn’t suitable if it is run a day later, e.g. on a Tuesday.

When I ran the above statement I got this error:

ERROR 2624: Column “d” does not exist

I had tried the dateadd calculation before my posting but for some reason it thinks the datepart of d is a variable?

This is the statement to calculate the Saturday and Sunday but again, I got the error ‘Column “wk” does not exist’

SELECT DATEADD(wk, DATEDIFF(wk, -2, CURRENT\_TIMESTAMP), -2) AS DAY\_OF\_WEEK /\* Saturday \*/

SELECT DATEADD(wk, DATEDIFF(wk, -1, CURRENT\_TIMESTAMP), -1) AS DAY\_OF\_WEEK /\* Sunday \*/

I’ve tried the statements below but get this error:

ERROR 4856: Syntax error at or near “@” at character 9

DECLARE @startdate datetime

select @startdate = DATEADD(wk, DATEDIFF(wk, -1, CURRENT\_TIMESTAMP), -1)

DECLARE @enddate datetime

select @enddate = DATEADD(wk, DATEDIFF(wk, -2, CURRENT\_TIMESTAMP), -2)

Sharon

---

<div class="post-metadata">

**Author:** ![GregDavis11009](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/gregdavis11009/32/122_2.png) [@GregDavis11009](https://forums.toadworld.com/u/GregDavis11009)\
**Post date:** [April 15, 2015, 4:28pm UTC](https://forums.toadworld.com/t/calculate-date-parameters-to-use-in-program/33122/4 "2015-04-15T16:28:30Z")

</div>

I don’t use HP vertica but it looks more like Oracle than SQL Server or Sybase. Try: between Next\_Day(getdate(), ‘Saturday’) - 14 and Next\_Day(getdate(), ‘Sunday’) - 7 . I am hoping it allows simple integer days add/subtract like Oracle but I have no idea. Good luck!

---

<div class="post-metadata">

**Author:** ![sharon.esler](https://avatars.discourse-cdn.com/v4/letter/s/f08c70/32.png) [@sharon.esler](https://forums.toadworld.com/u/sharon.esler)\
**Post date:** [April 15, 2015, 6:52pm UTC](https://forums.toadworld.com/t/calculate-date-parameters-to-use-in-program/33122/5 "2015-04-15T18:52:37Z")

</div>

Thanks Greg that worked well with

select Next\_Day(getdate(), ‘Sunday’) - 14 as start, Next\_Day(getdate(), ‘Saturday’) - 7 as end

returning

5/04/2015 and 11/04/2015 respectively.

Thanks again for your help.